Retrieve / Return LAST Row of Autofiltered data
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1

Retrieve / Return LAST Row of Autofiltered data
 

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?


Sam via OfficeKB.com wrote:
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

Retrieve / Return LAST Row of Autofiltered data
 

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!

In article <60d2f1fa408bc@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

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

Retrieve / Return LAST Row of Autofiltered data
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1

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