Anyone know how I can filter a list by a variable in a list and have cell A2
always show the latest variable the list is filtered by. The list would start
on say Row 4 so e.g Heading is Product, this column filtered to show the word
Health. I want this to automatically also appear in Cell A2. using Excel 2003.

Thanks in advance.

From Tom Ogilvy:

*******************************
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.
************************************************** ****
Note that the Subtotal function is used to re-calc the cell when the filter is changed - that won't
ahppen if you just us the ShowFilter function.

HTH,
Bernie
MS Excel MVP


" Inserting an option button in Word" soft.com
wrote in message ...
Anyone know how I can filter a list by a variable in a list and have cell A2
always show the latest variable the list is filtered by. The list would start
on say Row 4 so e.g Heading is Product, this column filtered to show the word
Health. I want this to automatically also appear in Cell A2. using Excel 2003.

Thanks in advance.

Thanks Bernie,

I'll try it now.

"Bernie Deitrick" wrote:

From Tom Ogilvy:

*******************************
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.
************************************************** ****
Note that the Subtotal function is used to re-calc the cell when the filter is changed - that won't
ahppen if you just us the ShowFilter function.

HTH,
Bernie
MS Excel MVP


" Inserting an option button in Word" soft.com
wrote in message ...
Anyone know how I can filter a list by a variable in a list and have cell A2
always show the latest variable the list is filtered by. The list would start
on say Row 4 so e.g Heading is Product, this column filtered to show the word
Health. I want this to automatically also appear in Cell A2. using Excel 2003.

Thanks in advance.