Offset on a Filtered List

[ryguy7272]

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--

 

[T. Valko]

Is B3 the column header?

 

[ryguy7272]

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---

 

[T. Valko]

Assume the data range is B4:B15.

Array entered** :

=INDEX(B4:B15,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B15,ROW(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)

 

[ryguy7272]

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--

 

[T. Valko]

You're welcome. Thanks for the feedback!