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

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

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

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

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

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