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 ?