Thread: Filter formula

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

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


"PO" wrote:

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

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



"CLR" wrote:

> 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
>
>
> "PO" wrote:
>
> > 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
> >
> >
> >

Hehe, no probs

/PO


"CLR" <CLR@discussions.microsoft.com> skrev i meddelandet
news:1DE21016-835D-4F74-8681-1443CB4630B4@microsoft.com...
> 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
>
>
>
> "CLR" wrote:
>
>> 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
>>
>>
>> "PO" wrote:
>>
>> > 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
>> >
>> >
>> >

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

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


PO wrote:
> 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
>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html