A list of what is being filtered on?

[Mel]

Is it possible to create a table/list of what is being filtered on a separate
tab or off to the side? My data has several col's and the filters are
populating a graph so I wanted to show what filtered results are driving the
graph results.

 

[Dave Peterson]

I saved this from a Tom Ogilvy post:
====================================

http://j-walk.com/ss/excel/usertips/tip044.htm
or
http://spreadsheetpage.com/index.php/tip/displaying_autofilter_criteria/

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
====================

You may want to put the formula above the cell with the autofilter arrow.

 

[Mel]

This was very helpful! However, do you know of a way to not have the = sign
return when your getting the filtered criteria?

 

[Dave Peterson]

I think I'd keep it there. If you drop the first character, how would you know
if you're using =, >, <, <>, etc.

But to skip the first character without changing the UDF, you can use a
worksheet function:
=mid(2,FilterCriteria(B5)&left(Subtotal(9,B5:B200),0),255)

where 255 is long enough to make sure you get the whole string.

 

[Mel]

Thank you! One last question: When I use multiple filters and then try to
clear them all out at once (data -filters - show all) the data will unfilter
but my new formulas will show as if it is still filtered. Is there a way
around this?

 

[Dave Peterson]

The =subtotal() stuff is trying to make the formula recalculate whenever the
filter is changed.

But it looks like =subtotal() doesn't help when you do data|showall.

You could hit the F9 key to force a manual recalc. Or just filter once more to
show All (in just a single column.

Thank you! One last question: When I use multiple filters and then try to
clear them all out at once (data -filters - show all) the data will unfilter
but my new formulas will show as if it is still filtered. Is there a way
around this?