formula to find the filter criteria

[tuser1]

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 ?

 

[Bob Phillips]

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+32),"=","")

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)

 

[tuser1]

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+32),"=","")

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+32),"=","")
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)

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 ?

 

[Bob Phillips]

It sounds as though you have put the routine in the correct place.

Take a look at Insert>Function, 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+32),"=","")

in your case.

--

HTH

RP
(remove nothere from the email address if mailing direct)

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+32),"=","")

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+32),"=","")
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)

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 ?

 

[tuser1]

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 Insert>Module 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.

 

[David McRitchie]

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

 

[tuser1]

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.

 

[Aladin Akyurek]

A possible formula approach:

=IF(SUBTOTAL(3,A2:A9)=COUNTA(A2:A9),
"",

INDEX(A2:A9,MATCH(1,SUBTOTAL(3,OFFSET(A2:A9,ROW(A2:A9)-ROW(A2),,1)),0)))

which must be confirmed with control+shift+enter.

 

[Bruce Kovacs]

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?

 

[Bob Phillips]

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]

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

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)

 

[Bob Phillips]

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)

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

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)

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


:

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+32),"=","")

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)

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 ?

 

[Bruce Kovacs]

OK. That makes sense. I guess I was looking for an explanation that was not
so obvious.

Thanks again.
--
Bruce Kovacs

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)

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

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)


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


:

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+32),"=","")

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)

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 ?