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 ?