If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need to clear controls of Filter form | Jan Il | Using Forms | 2 | November 28th, 2004 02:04 PM |
Filter and display rows on another worksheet | JML | General Discussion | 3 | June 11th, 2004 10:33 PM |
producing a report based on filter criteria... | Brad Pears | Setting Up & Running Reports | 3 | June 8th, 2004 04:27 PM |