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
Similar Threads
