Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I DISPLAY Filter Criteria
I want to be able to display the criteria that a user has selected while
using filters in Excel. The only way I currently know how to check my criteria is by looking for the blue arrows! |
#2
|
|||
|
|||
To show the value that has been selected in the AutoFilter dropdown, you
can create a User Defined Function. Tom Ogilvy posted the following function, that returns the criteria from a column in an autofiltered table. It will show both criteria if there are two, and includes the operator. David McRitchie has instructions for storing a macro: http://www.mvps.org/dmcritchie/excel....htm#havemacro On the worksheet, create a formula that refers to the cell that contains the Customer heading. For example, in cell H1, enter: =ShowFilter(A1)&CHAR(SUBTOTAL(9,A2)*0+32) '=============================================== Public Function ShowFilter(rng As Range) 'UDF that displays the filter criteria. 'posted by Tom Ogilvy 1/17/02 'To make it respond to a filter change, tie it to the subtotal command. '=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32) 'So the above would show the criteria for column B 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 '============================================== Scott Fendrich wrote: I want to be able to display the criteria that a user has selected while using filters in Excel. The only way I currently know how to check my criteria is by looking for the blue arrows! -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Stephen Bullen also has a UDF that'll display a filter's criteria. You can
find it on John Walkenbach's website: http://j-walk.com/ss/excel/usertips/tip044.htm HTH Jason Atlnata, GA "Scott Fendrich" wrote: I want to be able to display the criteria that a user has selected while using filters in Excel. The only way I currently know how to check my criteria is by looking for the blue arrows! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using advanced filter to search for criteria in a list | Excel Discussion (Misc queries) | |||
Filter Criteria | Excel Discussion (Misc queries) | |||
Improve autofilter combine conditionals with filter criteria | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Data Filter - Not all rows in spreadsheet will display in Autofilt | Excel Worksheet Functions |