Posted to microsoft.public.excel.worksheet.functions
|
|
Retrieve / Return LAST Row of Autofiltered data
Hi Domenic,
Thank you very much. The Formula works Great!
=INDEX(Store,MATCH(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store))
,0,1))),ROWS($A$2:A2))
...confirmed with CONTROL+SHIFT+ENTER.
Cheers,
Sam
Domenic wrote:
Assuming that you'd like to return the relevant values in Column A,
starting at A2, try...
A2, copied down:
=INDEX(Store,LOOKUP(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store)
),0,1)),ROW(Store)-MIN(ROW(Store))+1),ROWS($A$2:A2))
...confirmed with just ENTER, or
=INDEX(Store,MATCH(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store))
,0,1))),ROWS($A$2:A2))
...confirmed with CONTROL+SHIFT+ENTER.
Hope this helps!
Hi All,
[quoted text clipped - 4 lines]
Cheers,
Sam
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1
|