Shape & Slicer
- Thread starter Nrpatel
- Start date
Asheesh
Excel Ninja
Check this one...it should help
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim srch As String
Dim wsht As Worksheet
Dim isht As Worksheet
Dim lRow As Long
Dim DataRow As Long
Set isht = Sheets("Invoice")
If Not Intersect(Target, Target.Worksheet.[(E2)]) Is Nothing Then
srch = [(E2)].Value
For Each wsht In ThisWorkbook.Worksheets
If wsht.Name = isht.Name Then
GoTo NextSheet
Else
lRow = wsht.Cells(Rows.Count, "A").End(xlUp).Row
wsht.Range("A2:I" & lRow).AutoFilter Field:=9, Criteria1:=srch
If wsht.AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Count - 1 > 0 Then
DataRow = wsht.Range("A2").End(xlDown).Row
isht.Range("B2").Value = wsht.Cells(DataRow, "D")
isht.Range("E3").Value = wsht.Cells(DataRow, "B")
isht.Range("B6").Value = wsht.Cells(DataRow, "E")
isht.Range("C6").Value = wsht.Cells(DataRow, "C")
isht.Range("D6").Value = wsht.Cells(DataRow, "F")
Exit Sub
End If
End If
NextSheet:
Next wsht
End If
End SubAsheesh
Excel Ninja
It worked fine for me when I wrote it, however, I shall revisit it soon.
Regarding the Formula solution I am not sure if it can be done with a single formula but I can try..
let me call upon other experts to comment - @NARAYANK991 @Luke M @Hui
Regarding the Formula solution I am not sure if it can be done with a single formula but I can try..
let me call upon other experts to comment - @NARAYANK991 @Luke M @Hui
Asheesh is correct, a formula-only solution here will be near impossible due to bad workbook design. If you can change the design, I'd suggest using a single worksheet for all the data, rather than splitting it out by month. You already have a field that has the date in it, so there's no reason from data analyses viewpoint to have multiple worksheets.
What do you gain from having multiple month worksheets? It would be again be easier to start with all data in one location (and then split it out if needed) than trying to go the other way. It would also reduce overall file size since you only have one PivotTable as opposed to twelve.
Nevertheless, here's your file with a macro taken from Ron's site that will consolidate all the month's into the Master sheet. Then your formulas can be based off of the master sheet.
Nevertheless, here's your file with a macro taken from Ron's site that will consolidate all the month's into the Master sheet. Then your formulas can be based off of the master sheet.
Asheesh
Excel Ninja
What is wrong with this method..And I am not sure about your exact requirement, however, follow the below steps to generate the sequential numbers
Define Name sNum as
=MAX($A$1:$A1)+1
then in cell A2 type =sNum
Now, drag it down..
Note: Do not enter the formula in A1..start from cell A2 otherwise it will give the circular ref.
Define Name sNum as
=MAX($A$1:$A1)+1
then in cell A2 type =sNum
Now, drag it down..
Note: Do not enter the formula in A1..start from cell A2 otherwise it will give the circular ref.
Khalid NGO
Excel Ninja
Hi @Nrpatel,
I am not sure you are looking for this:
in I2 simply refer to column B:
=B2
set custom format:
yymmdd
copy down...
Regards,
PS.
1) I have removed your several duplicate posts in this thread, please do not post duplicate.
2) If someone provided you the solution, give him proper feedback.