referencing filtered data

  • Thread starter Thread starter Paul Fearnley
  • Start date Start date
P

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
 
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.
 
Many thanks,

Paul

T. Valko said:
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.
Click to expand...
 
Back
Top