Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter formula
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter formula
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter formula
Hehe, no probs :)
/PO "CLR" skrev i meddelandet ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filter formula
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilter with Subtotal Sumif | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
formula to find the filter criteria | Excel Worksheet Functions | |||
Advanced Filter criteria (formula) | Excel Worksheet Functions |