formula to find the filter criteria

T

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

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

tuser1

Bob said:
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
Click to expand...

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
Click to expand...
[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 ?
Click to expand...
Click to expand...
 
B

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)


Bob said:
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
Click to expand...

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
Click to expand...
[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 ?
Click to expand...
Click to expand...
Click to expand...
 
T

tuser1

Bob said:
It sounds as though you have put the routine in the correct place.
Click to expand...

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

tuser1

A

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

Guest

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

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

Guest

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 said:
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)
Click to expand...
 
B

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)


Bruce Kovacs said:
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 said:
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 said:
I have incorporated the ShowFilter utility into a number of my
Click to expand...
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
Click to expand...
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
Click to expand...
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 ?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Thanks again.
--
Bruce Kovacs


Bob Phillips said:
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 said:
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 said:
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 ?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Nesting the ROW() command into a Conditional Format Formula 4
combination of AutoFilter and array formula? 5
Calculating Project Time 1
Formula help.... 7
Lookup Function Referencing cells, not text values 3
Forecast v actual - row addition problem 2
SORT data containing formula 5
formula that writes to a *different* cell? 4

Top