Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Aladin Akyurek
 
Posts: n/a
Default 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

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Domenic
 
Posts: n/a
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Sam via OfficeKB.com
 
Posts: n/a
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup & Return Range of Data shehasclass Excel Discussion (Misc queries) 2 April 6th 06 04:17 PM
Retrieve data from separate Excel session JessK Charts and Charting in Excel 2 March 27th 06 01:34 AM
Retrieve data from all of the worksheet within the file dannyboy213 Excel Worksheet Functions 1 March 22nd 06 07:51 PM
From several workbooks onto one excel worksheet steve Excel Discussion (Misc queries) 6 December 1st 05 09:03 AM
Return result only if data complies. John Moore Excel Discussion (Misc queries) 3 November 8th 05 03:28 PM


All times are GMT +1. The time now is 11:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"