| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Offset on a Filtered List
I am trying to find the value of the cell directly below cell B3 in a
filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Offset on a Filtered List
Is B3 the column header?
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I am trying to find the value of the cell directly below cell B3 in a filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Offset on a Filtered List
Yes! Exactly! B3 is a header. I have some info. in the first two rows, and
the headers are in row three and the data starts in row four. Any suggestions as to how to use a dynamic offset??? I would like the value that shows up in the cell right below B3 to also show in B1. In one of my examples, when I apply the filter the cell that is displayed right below B3 is B14 and in another example, when I choose a different criteria to filter by, the cell that is displayed right below B3 is B101. Again, in B1, I would like to display the value that shows up in the cell right below B3. Having worked with Excel for several years, I have seen the app. do many amazing things. I'm sure it is capable of this too...I just don't know how to do it!! Appreciate any help, Ryan--- -- RyGuy "T. Valko" wrote: Is B3 the column header? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I am trying to find the value of the cell directly below cell B3 in a filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Offset on a Filtered List
Assume the data range is B4:B15.
Array entered** : =INDEX(B4:B15,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B15,RO W(B4:B15)-MIN(ROW(B4:B15)),0,1)))*(B4:B15<""),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Yes! Exactly! B3 is a header. I have some info. in the first two rows, and the headers are in row three and the data starts in row four. Any suggestions as to how to use a dynamic offset??? I would like the value that shows up in the cell right below B3 to also show in B1. In one of my examples, when I apply the filter the cell that is displayed right below B3 is B14 and in another example, when I choose a different criteria to filter by, the cell that is displayed right below B3 is B101. Again, in B1, I would like to display the value that shows up in the cell right below B3. Having worked with Excel for several years, I have seen the app. do many amazing things. I'm sure it is capable of this too...I just don't know how to do it!! Appreciate any help, Ryan--- -- RyGuy "T. Valko" wrote: Is B3 the column header? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I am trying to find the value of the cell directly below cell B3 in a filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Offset on a Filtered List
UNREAL!! Just UNREAL!!
Biff, you helped me a couple of times before too. Thanks for all the functions from those previous times too. Regards, Ryan-- -- RyGuy "T. Valko" wrote: Assume the data range is B4:B15. Array entered** : =INDEX(B4:B15,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B15,RO W(B4:B15)-MIN(ROW(B4:B15)),0,1)))*(B4:B15<""),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Yes! Exactly! B3 is a header. I have some info. in the first two rows, and the headers are in row three and the data starts in row four. Any suggestions as to how to use a dynamic offset??? I would like the value that shows up in the cell right below B3 to also show in B1. In one of my examples, when I apply the filter the cell that is displayed right below B3 is B14 and in another example, when I choose a different criteria to filter by, the cell that is displayed right below B3 is B101. Again, in B1, I would like to display the value that shows up in the cell right below B3. Having worked with Excel for several years, I have seen the app. do many amazing things. I'm sure it is capable of this too...I just don't know how to do it!! Appreciate any help, Ryan--- -- RyGuy "T. Valko" wrote: Is B3 the column header? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I am trying to find the value of the cell directly below cell B3 in a filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Offset on a Filtered List
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... UNREAL!! Just UNREAL!! Biff, you helped me a couple of times before too. Thanks for all the functions from those previous times too. Regards, Ryan-- -- RyGuy "T. Valko" wrote: Assume the data range is B4:B15. Array entered** : =INDEX(B4:B15,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B15,RO W(B4:B15)-MIN(ROW(B4:B15)),0,1)))*(B4:B15<""),0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... Yes! Exactly! B3 is a header. I have some info. in the first two rows, and the headers are in row three and the data starts in row four. Any suggestions as to how to use a dynamic offset??? I would like the value that shows up in the cell right below B3 to also show in B1. In one of my examples, when I apply the filter the cell that is displayed right below B3 is B14 and in another example, when I choose a different criteria to filter by, the cell that is displayed right below B3 is B101. Again, in B1, I would like to display the value that shows up in the cell right below B3. Having worked with Excel for several years, I have seen the app. do many amazing things. I'm sure it is capable of this too...I just don't know how to do it!! Appreciate any help, Ryan--- -- RyGuy "T. Valko" wrote: Is B3 the column header? -- Biff Microsoft Excel MVP "ryguy7272" wrote in message ... I am trying to find the value of the cell directly below cell B3 in a filtered list; the list will change constantly as new criteria are selected. How can I do that? Thanks, Ryan-- -- RyGuy |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to add up the result of a filtered list | Excel Worksheet Functions | |||
| Edit filtered list | Excel Discussion (Misc queries) | |||
| 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) | |||
Linear Mode
