Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy name of filtered cell to another worksheet
Hello all
This is the scenario: WorksheetA - have an autofielter WorksheetB - one to get the autofiltered name from the WorksheetA WorksheetA - Business Name = Dell WorksheetB - C3= 'WorksheetA'!A2 This means C3=Dell BUT if I change the filtering in WorksheetA - Business Name = Microsoft my WorksheetB still showing the "Dell" name...I can tell the cell in WB is not changing as my data change in WA. My question is: how can I make this dynamically so my cell in WB change as my filter changes? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy name of filtered cell to another worksheet
Can you use the criteria for that column?
If yes... This is from a Tom Ogilvy post: http://j-walk.com/ss/excel/usertips/tip044.htm to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) Dim filt As Filter Dim sCrit1 As String Dim sCrit2 As String Dim sop As String Dim lngOp As Long Dim lngOff As Long Dim frng As Range Dim sh As Worksheet Set sh = rng.Parent If sh.FilterMode = False Then ShowFilter = "No Active Filter" Exit Function End If Set frng = sh.AutoFilter.Range If Intersect(rng.EntireColumn, frng) Is Nothing Then ShowFilter = CVErr(xlErrRef) Else lngOff = rng.Column - frng.Columns(1).Column + 1 If Not sh.AutoFilter.Filters(lngOff).On Then ShowFilter = "No Conditions" Else Set filt = sh.AutoFilter.Filters(lngOff) On Error Resume Next sCrit1 = filt.Criteria1 sCrit2 = filt.Criteria2 lngOp = filt.Operator If lngOp = xlAnd Then sop = " And " ElseIf lngOp = xlOr Then sop = " or " Else sop = "" End If ShowFilter = sCrit1 & sop & sCrit2 End If End If End Function =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm erick-flores wrote: Hello all This is the scenario: WorksheetA - have an autofielter WorksheetB - one to get the autofiltered name from the WorksheetA WorksheetA - Business Name = Dell WorksheetB - C3= 'WorksheetA'!A2 This means C3=Dell BUT if I change the filtering in WorksheetA - Business Name = Microsoft my WorksheetB still showing the "Dell" name...I can tell the cell in WB is not changing as my data change in WA. My question is: how can I make this dynamically so my cell in WB change as my filter changes? Thanks -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy cell from one worksheet to another worksheet in exel | Excel Discussion (Misc queries) | |||
how do i copy data from the top cell of a filtered list to anothe. | Excel Worksheet Functions | |||
copy data in a cell from worksheet A to worksheet B | Excel Discussion (Misc queries) | |||
How do I copy a date in a worksheet cell to another worksheet? | Excel Worksheet Functions | |||
How do I copy a filtered subset of data to another worksheet? | Excel Discussion (Misc queries) |