L
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.
value?
Column D1 is where I want to filter is located and it has 200 rows.
Thanks.
D
Dave Peterson
Nope. You'll need VBA.
T
T. Valko
If they mean to return the first item from a filtered list...
Array entered** :
=INDEX(D2
15,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.
Array entered** :
=INDEX(D2
15,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
D2
15.
O
ozgrid.com
No, you need a Custom Function
http://www.ozgrid.com/VBA/autofilter-criteria.htm
http://www.ozgrid.com/VBA/autofilter-criteria.htm
D
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.
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
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.
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.
D
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!
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!
T. Valko said: