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!

 

[NeosCoeus]

Hey Ryan,

I read this reply and this is exactly what I was looking to do as well. The formula works perfectly, so thank you for that. I have one follow up question though.

My data range was from B3:B64. When I enter those ranges into the formula I get back the expected results I wanted when I filter on column B. This data, however, gets updated on a regular basis that I just copy and paste into this column. So now the data ranges is from B3:B64, but tomorrow I'll copy and paste in information with a data range from B3:B90, then next week it'll be B3:B100 (as an example).

Is it possible to add or manipulate something with this formula to account for the fluctuations in the data range? Basically I don't want to have to go back into the formula each time and change the data range.

Thanks,
Kris

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)


--
Biff
Microsoft Excel MVP


"ryguy7272" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news[email protected]...
>> >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
>>
>>
>>