function to reference value selected with autofilter

  • Thread starter Thread starter cm
  • Start date Start date
C

cm

A single column value has been selected with the autofilter at the top of the
column. Is there a way to store that selected value in a cell to reference it
with functions?
 
This array formula will return the *first* item in the range D2:D15.

=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,ROW(D2:D15)-MIN(ROW(D2:D15))+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.
 
I got this function from T.Valko a while back
=INDEX(B4:B620,MATCH(1,(SUBTOTAL(3,OFFSET(B4:B620,ROW(B4:B620)-MIN(ROW(B4:B620)),0,1)))*(B4:B620<>""),0))

Hope that does what you want.

Ryan--
 
Back
Top