Display Chosen Autofilter Value

[LP]

Without going into VB, is there a way to display the chosen autofilter
value?

Column D1 is where I want to filter is located and it has 200 rows.
Thanks.

 

[Dave Peterson]

Nope. You'll need VBA.

Without going into VB, is there a way to display the chosen autofilter
value?

Column D1 is where I want to filter is located and it has 200 rows.
Thanks.

 

[T. Valko]

If they mean to return the first item from a filtered list...

Array entered** :

=INDEX(D215,MATCH(1,SUBTOTAL(3,OFFSET(D215,,,ROW(D215)-ROW(D2)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

That will return the first item from the filtered (or unfiltered) range
D215.

 

[ozgrid.com]

No, you need a Custom Function
http://www.ozgrid.com/VBA/autofilter-criteria.htm

 

[Dave Peterson]

Unless the user is showing blank cells and those cells are really empty <vbg>.

Seriously, Biff's formula won't show you the criteria unless it's pretty
simple. All those contains, greater than, does not... would return a value, but
maybe not what you're looking for.

 

[T. Valko]

All those contains, greater than, does not...

If that's what they want, the filter criteria, then I got nothin'!

Here's what the formula I suggested will do...

D1 = column header = Car Make
D2 = Chevy
D3 = Ford
D4 = Ford
D5 = Chevy
D6 = Chrysler

If you filter that column on Car Make>Chrysler then the formula will return
Chrysler.

 

[Dave Peterson]

Sometimes, when I was working with data I wasn't sure about was to add a column
of 1's and label it Counter.

Then each row of data had at least one field that was always there--no matter
how I filtered the data.

And it did make it easier to count the rows that were visible after the filter
was applied.

I know my co-workers didn't appreciate that extra column...until they actually
used it!

All those contains, greater than, does not...

If that's what they want, the filter criteria, then I got nothin'!

Here's what the formula I suggested will do...

D1 = column header = Car Make
D2 = Chevy
D3 = Ford
D4 = Ford
D5 = Chevy
D6 = Chrysler

If you filter that column on Car Make>Chrysler then the formula will return
Chrysler.