Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup & Return Range of Data | Excel Discussion (Misc queries) | |||
Retrieve data from separate Excel session | Charts and Charting in Excel | |||
Retrieve data from all of the worksheet within the file | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Return result only if data complies. | Excel Discussion (Misc queries) |