Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
Is it possible to create a table/list of what is being filtered on a separate
tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
I saved this from a Tom Ogilvy post:
==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_criteria/ to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) 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 =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ==================== You may want to put the formula above the cell with the autofilter arrow. Mel wrote: Is it possible to create a table/list of what is being filtered on a separate tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
This was very helpful! However, do you know of a way to not have the = sign
return when your getting the filtered criteria? "Dave Peterson" wrote: I saved this from a Tom Ogilvy post: ==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_criteria/ to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) 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 =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ==================== You may want to put the formula above the cell with the autofilter arrow. Mel wrote: Is it possible to create a table/list of what is being filtered on a separate tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
I think I'd keep it there. If you drop the first character, how would you know
if you're using =, , <, <, etc. But to skip the first character without changing the UDF, you can use a worksheet function: =mid(2,FilterCriteria(B5)&left(Subtotal(9,B5:B200) ,0),255) where 255 is long enough to make sure you get the whole string. Mel wrote: This was very helpful! However, do you know of a way to not have the = sign return when your getting the filtered criteria? "Dave Peterson" wrote: I saved this from a Tom Ogilvy post: ==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_criteria/ to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) 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 =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ==================== You may want to put the formula above the cell with the autofilter arrow. Mel wrote: Is it possible to create a table/list of what is being filtered on a separate tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
Thank you! One last question: When I use multiple filters and then try to
clear them all out at once (data -filters - show all) the data will unfilter but my new formulas will show as if it is still filtered. Is there a way around this? "Dave Peterson" wrote: I think I'd keep it there. If you drop the first character, how would you know if you're using =, , <, <, etc. But to skip the first character without changing the UDF, you can use a worksheet function: =mid(2,FilterCriteria(B5)&left(Subtotal(9,B5:B200) ,0),255) where 255 is long enough to make sure you get the whole string. Mel wrote: This was very helpful! However, do you know of a way to not have the = sign return when your getting the filtered criteria? "Dave Peterson" wrote: I saved this from a Tom Ogilvy post: ==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_criteria/ to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) 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 =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ==================== You may want to put the formula above the cell with the autofilter arrow. Mel wrote: Is it possible to create a table/list of what is being filtered on a separate tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
A list of what is being filtered on?
The =subtotal() stuff is trying to make the formula recalculate whenever the
filter is changed. But it looks like =subtotal() doesn't help when you do data|showall. You could hit the F9 key to force a manual recalc. Or just filter once more to show All (in just a single column. Mel wrote: Thank you! One last question: When I use multiple filters and then try to clear them all out at once (data -filters - show all) the data will unfilter but my new formulas will show as if it is still filtered. Is there a way around this? "Dave Peterson" wrote: I think I'd keep it there. If you drop the first character, how would you know if you're using =, , <, <, etc. But to skip the first character without changing the UDF, you can use a worksheet function: =mid(2,FilterCriteria(B5)&left(Subtotal(9,B5:B200) ,0),255) where 255 is long enough to make sure you get the whole string. Mel wrote: This was very helpful! However, do you know of a way to not have the = sign return when your getting the filtered criteria? "Dave Peterson" wrote: I saved this from a Tom Ogilvy post: ==================================== http://j-walk.com/ss/excel/usertips/tip044.htm or http://spreadsheetpage.com/index.php...lter_criteria/ to get it to refresh: =FilterCriteria(B5)&left(Subtotal(9,B5:B200),0) this is one I wrote back in 2000 Here is a user defined function that will display the criteria in a cell: Public Function ShowFilter(rng As Range) 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 =ShowFilter(B5)&left(Subtotal(9,B5:B200),0) would show the filter for column 2 I usually put these functions in cells above the filter ============== If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm ==================== You may want to put the formula above the cell with the autofilter arrow. Mel wrote: Is it possible to create a table/list of what is being filtered on a separate tab or off to the side? My data has several col's and the filters are populating a graph so I wanted to show what filtered results are driving the graph results. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
charting a filtered list | Excel Discussion (Misc queries) | |||
Return Value from a Filtered List | Excel Worksheet Functions | |||
Edit filtered list | Excel Discussion (Misc queries) | |||
Counting a Filtered List | Excel Discussion (Misc queries) | |||
Using TRIMEAN on a filtered list | Excel Discussion (Misc queries) |