Filter formula

P

PO

Hi,

I have a autofiltered table with many columns.
I would like to display which columns are filtered (and the criteria used)
in cell A2. Is there a formula that returnes the filters used?

Regards
PO
 
C

CLR

I know of no way to do exactly what you ask. For my own purposes, I use a
two-row header column on my database, and modify the AutoFilter macros as
shown herein to color either BOTH of the cells in the header of the Key1
filter column, or only ONE of the cells in the header of the Key2 filter
column. It's a little involved, but works pretty good and looks cool
too......here's two macros so you can see the difference between two
AutoFilter color schemes..........each macro first clears the previous
coloration, then instills it's own........

Sub MachinePN()
Rows("3:6").Select
Selection.Interior.ColorIndex = xlNone
Application.Goto Reference:="Database"
Selection.Sort Key1:=Range("v7"), Order1:=xlAscending, Key2:=Range("E7") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 1
Range("v5:v6").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("E6").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D1").Select
End Sub


Sub PartNumberOPcode()
Rows("3:6").Select
Selection.Interior.ColorIndex = xlNone
Application.Goto Reference:="Database"
Selection.Sort Key1:=Range("E7"), Order1:=xlAscending, Key2:=Range("G7") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1,
MatchCase:=False, _
Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 1
Range("E5:E6").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("G6").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("D1").Select
End Sub


hth
Vaya con Dios,
Chuck, CABGx3
 
C

CLR

Sorry PO........I read "Autofilter" but was thinking "Sort".......my macros
were used to sort a database and report the columns sorted upon rather than
for the AutoFilter........my mistake <blush>.........I'm going to finish
taking all my medication now.........

Vaya con Dios,
Chuck, CABGx3
 
P

PO

Hehe, no probs :)

/PO


CLR said:
Sorry PO........I read "Autofilter" but was thinking "Sort".......my
macros
were used to sort a database and report the columns sorted upon rather
than
for the AutoFilter........my mistake <blush>.........I'm going to finish
taking all my medication now.........

Vaya con Dios,
Chuck, CABGx3
Click to expand...
 
D

Debra Dalgleish

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/getstarted.htm#havemacro

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