Retrieve / Return LAST Row of Autofiltered data

[Sam via OfficeKB.com]

Hi All,

Is there a Formula that can index the Named Range "Store" and retrieve its
"LAST Row" of autofiltered data (visible cells only), and Return the
autofiltered data to / down a Single Column?

Cheers,
Sam

 

[Aladin Akyurek]

Hard to visualize what you are asking for...

What is the reference of Store?

If it's a single-column vertical range, "its LAST row" will be a single
cell. Moreoever, is the range nummeric or text?

 

[Domenic]

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!

 

[Sam via OfficeKB.com]

Hi Aladin,

Oops! The Named Range "Store" spans 55 Columns and many Rows. "Store"
contains numeric data.

Hard to visualize what you are asking for...

Apologies!

Cheers,
Sam

 

[Sam via OfficeKB.com]

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

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