Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null | Excel Discussion (Misc queries) | |||
I want to add up the result of a filtered list | Excel Worksheet Functions | |||
filtered list question | Excel Discussion (Misc queries) | |||
Counting a Filtered List | Excel Discussion (Misc queries) | |||
Using TRIMEAN on a filtered list | Excel Discussion (Misc queries) |