referencing filtered data

[Paul Fearnley]

I have filtered a large table according to the values in the first column
and I now want to refer to that filtered value in a formula at the bottom of
the filtered data.

If the filtered value is 2 I want to use 2 in a formula, and if I then
filter on the value 3, I want the same formula to then pick up the value 3.

Any ideas?

I am using Office 2007.

Thanks,

Paul

 

[T. Valko]

This array formula** will return the value of the first visible cell in the
filtered (or unfiltered) range.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,ROW(A2:A15)-ROW(A2)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Paul Fearnley]

Many thanks,

Paul

This array formula** will return the value of the first visible cell in
the filtered (or unfiltered) range.

=INDEX(A2:A15,MATCH(1,SUBTOTAL(3,OFFSET(A2:A15,,,ROW(A2:A15)-ROW(A2)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT key then hit ENTER.

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Many thanks,

Paul