Return Value from a Filtered List
 

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

Return Value from a Filtered List
 

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

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.


wrote:
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.

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Return Value from a Filtered List
 

Assume row A1:D1 are column headers with the filter applied.
A2:D20 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,RO W(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


wrote in message
oups.com...
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

Return Value from a Filtered List
 

On Aug 6, 8:16 pm, "T. Valko" wrote:
Assume row A1:D1 are column headers with the filter applied.
A2:D20 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,RO W(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

wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

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

Conor

Return Value from a Filtered List
 

wrote in message
oups.com...
On Aug 6, 8:16 pm, "T. Valko" wrote:
Assume row A1:D1 are column headers with the filter applied.
A2:D20 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,RO W(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

wrote in message

oups.com...



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- Hide quoted text -

- Show quoted text -

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

Conor

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP