Return Value from a Filtered List

[conorfinnegan]

Thanks in advance to anyone that can help me. I have chart that I
would like to title based on a user's selection from a filtered list.
The title is identical to whatever the user picks from an AutoFiltered
List. I am having trouble referencing the cell using dynamic titles
because it never returns the value in the same position (the filter
shows values but also has many hidden rows). I thought if I could
populate the user's selection in another field and then reference this
other field, that would help me title my graph.

Does anyone know how to do this?

Please let me know either way.

Thanks.

Conor Finnegan

 

[Debra Dalgleish]

To show the value that has been selected in the AutoFilter dropdown, you
can create a User Defined Function. There's a sample file here:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0007 - Highlight AutoFilter Headings'

In cells I1:O1 are formulas that return the filter criteria, based on a
user defined function that was posted by Tom Ogilvy.
You could adapt that to your workbook.

 

[T. Valko]

Assume row A11 are column headers with the filter applied.
A220 is the data

Try this array formula** to return the first filtered value from column A:

=INDEX(A2:A20,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-MIN(ROW(A2:A20)),0,1)))*(A2:A20<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[conorfinnegan]

Assume row A11 are column headers with the filter applied.
A220 is the data

Try this array formula** to return the first filtered value from column A:

=INDEX(A2:A20,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-MIN(ROW(A2:A20)­),0,1)))*(A2:A20<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP








- Show quoted text -

Thank you both for your help. The ideas worked great. I really do
appreciate it.

Conor

 

[T. Valko]

Assume row A11 are column headers with the filter applied.
A220 is the data

Try this array formula** to return the first filtered value from column
A:

=INDEX(A2:A20,MATCH(1,(SUBTOTAL(3,OFFSET(A2:A20,ROW(A2:A20)-MIN(ROW(A2:A20)­),0,1)))*(A2:A20<>""),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP








- Show quoted text -

You're welcome. Thanks for the feedback!