Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
I have a worksheet which has in column A2:A9 a label and in column
B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this
post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
Bob Phillips wrote:
What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is Public Function ShowFilter(rng As Range) Dim filt As Filter [snip] End Function I have pasted the Public Function into the empty window which appeared when I selected the menu "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)". Then I pasted your formula =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") into cell C1, but unfortunately this gave me an error "#NAME?". I am a newbie when it comes to Excel programming, but I suspect that either "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)" is not the right place for the Public Function or if it is, then something needs to be compiled first to make it work. Did I paste the Public Function to the right place / how can I make this work ? I am Using Microsoft Excel 2000. -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column [snip] In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
It sounds as though you have put the routine in the correct place.
Take a look at InsertFunction, and in the left listbox scroll-down to and select User Defined from that list and see if your function appears in the right listbox. I have just re-done it and it is still working fine. Also, do an F9, to make sure that the calls are all invoked to start. And finally, the formula should be =SUBSTITUTE(showfilter(A1)&CHAR(SUBTOTAL(9,A2)*0+3 2),"=","") in your case. -- HTH RP (remove nothere from the email address if mailing direct) wrote in message oups.com... Bob Phillips wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is Public Function ShowFilter(rng As Range) Dim filt As Filter [snip] End Function I have pasted the Public Function into the empty window which appeared when I selected the menu "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)". Then I pasted your formula =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") into cell C1, but unfortunately this gave me an error "#NAME?". I am a newbie when it comes to Excel programming, but I suspect that either "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)" is not the right place for the Public Function or if it is, then something needs to be compiled first to make it work. Did I paste the Public Function to the right place / how can I make this work ? I am Using Microsoft Excel 2000. -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column [snip] In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
Bob Phillips wrote: It sounds as though you have put the routine in the correct place. I have found what the problem was: When I went "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)", I found a blank window titled "Sheet1 (Code)". It seems to me that this window is basically dead, at least the code for the UDF pasted here is not picked up. In order to make the UDF work, I now have to go InsertModule and paste in the code in a new window, which looks very much like the other "Sheet1 (Code)" window, except that its title is "Module1 (Code)". Now it works like a charm! Thanks a lot. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
Into a module is the correct place, the ones marked sheet
are not dead they are for worksheet event macros and apply only to the that sheet. You can read about Event macros http://www.mvps.org/dmcritchie/excel/event.htm and you can read more about macros installed into standard modules like what you had, at http://www.mvps.org/dmcritchie/excel/getstarted.htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm wrote in message ups.com... Bob Phillips wrote: It sounds as though you have put the routine in the correct place. I have found what the problem was: When I went "Tools" / "Macro" / "Visual Basic Editor (Alt+F11)", I found a blank window titled "Sheet1 (Code)". It seems to me that this window is basically dead, at least the code for the UDF pasted here is not picked up. In order to make the UDF work, I now have to go InsertModule and paste in the code in a new window, which looks very much like the other "Sheet1 (Code)" window, except that its title is "Module1 (Code)". Now it works like a charm! Thanks a lot. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
David McRitchie wrote: Into a module is the correct place, the ones marked sheet are not dead they are for worksheet event macros and apply only to the that sheet. You can read about Event macros http://www.mvps.org/dmcritchie/excel/event.htm and you can read more about macros installed into standard modules like what you had, at http://www.mvps.org/dmcritchie/excel/getstarted.htm --- Thanks a lot. This tutorial is exactly what I need to get me started with programming in Excel. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
I have incorporated the ShowFilter utility into a number of my worksheets and
it does just what I wanted, with one exception. When filtering a column of dates and selecting the "is greater than or equal to" option, the ShowFilter result displays the date value instead of the formatted date (e.g., =38371 instead of =19-Jan-05). The value does show correctly when just one date is selected (e.g., =19-Jan-05). Is there a way to work around this issue? -- Bruce Kovacs "Bob Phillips" wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
Bruce,
I would hope there is a better way, but try this 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 = Transform(filt.Criteria1, rng(2)) sCrit2 = Transform(filt.Criteria2, rng(2)) 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 Function Transform(Crit As String, cell As Range) Dim i As Long Do i = i + 1 Loop Until Not Mid(Crit, i, 1) Like "[=<]" If i 0 Then Transform = Left(Crit, i - 1) & Format(Right(Crit, Len(Crit) - i + 1), cell.NumberFormat) End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... I have incorporated the ShowFilter utility into a number of my worksheets and it does just what I wanted, with one exception. When filtering a column of dates and selecting the "is greater than or equal to" option, the ShowFilter result displays the date value instead of the formatted date (e.g., =38371 instead of =19-Jan-05). The value does show correctly when just one date is selected (e.g., =19-Jan-05). Is there a way to work around this issue? -- Bruce Kovacs "Bob Phillips" wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
Thanks. That did the trick.
I understand what you are doing except for the modified line "sCrit1 = Transform(filt.Criteria1, rng(2))". Why the "2" for the range? -- Bruce Kovacs "Bob Phillips" wrote: Bruce, I would hope there is a better way, but try this 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 = Transform(filt.Criteria1, rng(2)) sCrit2 = Transform(filt.Criteria2, rng(2)) 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 Function Transform(Crit As String, cell As Range) Dim i As Long Do i = i + 1 Loop Until Not Mid(Crit, i, 1) Like "[=<]" If i 0 Then Transform = Left(Crit, i - 1) & Format(Right(Crit, Len(Crit) - i + 1), cell.NumberFormat) End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... I have incorporated the ShowFilter utility into a number of my worksheets and it does just what I wanted, with one exception. When filtering a column of dates and selecting the "is greater than or equal to" option, the ShowFilter result displays the date value instead of the formatted date (e.g., =38371 instead of =19-Jan-05). The value does show correctly when just one date is selected (e.g., =19-Jan-05). Is there a way to work around this issue? -- Bruce Kovacs "Bob Phillips" wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
That is to get the second cell in the range being filtered, as I am using
the format of that cell. I don't want the first cell, as that is the heading cell, which will not have a date format. -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... Thanks. That did the trick. I understand what you are doing except for the modified line "sCrit1 = Transform(filt.Criteria1, rng(2))". Why the "2" for the range? -- Bruce Kovacs "Bob Phillips" wrote: Bruce, I would hope there is a better way, but try this 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 = Transform(filt.Criteria1, rng(2)) sCrit2 = Transform(filt.Criteria2, rng(2)) 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 Function Transform(Crit As String, cell As Range) Dim i As Long Do i = i + 1 Loop Until Not Mid(Crit, i, 1) Like "[=<]" If i 0 Then Transform = Left(Crit, i - 1) & Format(Right(Crit, Len(Crit) - i + 1), cell.NumberFormat) End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... I have incorporated the ShowFilter utility into a number of my worksheets and it does just what I wanted, with one exception. When filtering a column of dates and selecting the "is greater than or equal to" option, the ShowFilter result displays the date value instead of the formatted date (e.g., =38371 instead of =19-Jan-05). The value does show correctly when just one date is selected (e.g., =19-Jan-05). Is there a way to work around this issue? -- Bruce Kovacs "Bob Phillips" wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula to find the filter criteria
OK. That makes sense. I guess I was looking for an explanation that was not
so obvious. Thanks again. -- Bruce Kovacs "Bob Phillips" wrote: That is to get the second cell in the range being filtered, as I am using the format of that cell. I don't want the first cell, as that is the heading cell, which will not have a date format. -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... Thanks. That did the trick. I understand what you are doing except for the modified line "sCrit1 = Transform(filt.Criteria1, rng(2))". Why the "2" for the range? -- Bruce Kovacs "Bob Phillips" wrote: Bruce, I would hope there is a better way, but try this 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 = Transform(filt.Criteria1, rng(2)) sCrit2 = Transform(filt.Criteria2, rng(2)) 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 Function Transform(Crit As String, cell As Range) Dim i As Long Do i = i + 1 Loop Until Not Mid(Crit, i, 1) Like "[=<]" If i 0 Then Transform = Left(Crit, i - 1) & Format(Right(Crit, Len(Crit) - i + 1), cell.NumberFormat) End If End Function -- HTH RP (remove nothere from the email address if mailing direct) "Bruce Kovacs" wrote in message ... I have incorporated the ShowFilter utility into a number of my worksheets and it does just what I wanted, with one exception. When filtering a column of dates and selecting the "is greater than or equal to" option, the ShowFilter result displays the date value instead of the formatted date (e.g., =38371 instead of =19-Jan-05). The value does show correctly when just one date is selected (e.g., =19-Jan-05). Is there a way to work around this issue? -- Bruce Kovacs "Bob Phillips" wrote: What you want is Tom Ogilvy's wonderful Showfilter utility, appended to this post. You would use it like this =SUBSTITUTE(showfilter(L1)&CHAR(SUBTOTAL(9,L2)*0+3 2),"=","") The CHAR(SUBTOTAL bit is just to force a recalc if the criteria are changed The UDF is 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 -- HTH Bob Phillips (remove nothere from email address if mailing direct) wrote in message oups.com... I have a worksheet which has in column A2:A9 a label and in column B2:B9 a value, like so: A1="LABEL" B1="VALUE" A2="LabelGreen" B2=452.47 A3="LabelBlue" B3=-87 A4="LabelRed" B4=9 A5="LabelGreen" B5=-7 A6="LabelBlue" B6=1888.97 A7="LabelRed" B7=144 A8="LabelGreen" B8=-0.02 A9="LabelBlue" B9=87002 I use the autofilter on column A to select only certain labels. in cell D1 I use the subtotal formula to give me the sum of all filtered values. D1=SUBTOTAL(9,B2:B9) This works fine, for example, if I filter on column A for "LabelGreen", the formula in D1 calculates 452.47 - 7 - 0.02 = 445.45. What I am trying to do now and what I have not yet found a solution for is: In cell C1 I want to display the criteria I have filtered for. For example, when I filter column A for "LabelGreen", I want "LabelGreen" to be displayed in cell C1. I was thinking something similar to SUBTOTAL might do the trick, for example: C1=SUBFIRST(9,A2:A9) But this only gives me (and I am not surprised) an error "#NAME?" Does anybody have an idea ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database functions should use criteria in formula, as 1-2-3 does | Excel Worksheet Functions | |||
Data Validation - Scroll in the formula bar for a custom criteria | Excel Worksheet Functions | |||
Array Formula w/ Multiple SumIf Criteria | Excel Worksheet Functions | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions | |||
"find" and "filter" working weirdly | Excel Discussion (Misc queries) |