| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display next to last results
This formula works great to find the last entry in a col.
=LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
you can use
=indirect("Sheet2!A"&counta(Sheet2!a:a)-1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Luke" escreveu: This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
If your data in Column A has no gaps in it, this formula can be used...
=OFFSET(A1,COUNTA(A:A)-2,0) If there are (or can be) gaps in your data, then this formula can be used... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) Rick "Luke" wrote in message ... This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
=OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0)
That will return incorrect results. Assuming there will be at least 2 non-empty cells. Array entered** : =INDEX(A:A,LARGE(IF(A1:A100<"",ROW(A1:A100)),2)) Since this is an array formula the inner range references can not be entire columns (unless you're using Excel 2007). ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... If your data in Column A has no gaps in it, this formula can be used... =OFFSET(A1,COUNTA(A:A)-2,0) If there are (or can be) gaps in your data, then this formula can be used... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) Rick "Luke" wrote in message ... This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
Marcello, the formula returned a blank cell. I think because it is looking
for text and my data is numeric. However I do have blank cells in the column. Any thoughts? Luke "Marcelo" wrote: you can use =indirect("Sheet2!A"&counta(Sheet2!a:a)-1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Luke" escreveu: This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
Yes, that is true if there is a blank space in the row immediately in front
of the last data item. Good catch. Here is a modification to what I posted that should work and is non-array entered... =OFFSET(A1,MATCH(LOOKUP(2,1/(INDIRECT("A1:A"&(MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-1))<""),A:A),A:A,0)-1,0) Yeah, it is longer than your array-entered offering, but it seems to work. Rick "T. Valko" wrote in message ... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) That will return incorrect results. Assuming there will be at least 2 non-empty cells. Array entered** : =INDEX(A:A,LARGE(IF(A1:A100<"",ROW(A1:A100)),2)) Since this is an array formula the inner range references can not be entire columns (unless you're using Excel 2007). ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... If your data in Column A has no gaps in it, this formula can be used... =OFFSET(A1,COUNTA(A:A)-2,0) If there are (or can be) gaps in your data, then this formula can be used... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) Rick "Luke" wrote in message ... This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
If you want non-array entered, then try this one
=INDEX(A:A,LARGE(INDEX((A1:A100<"")*ROW(A1:A100), ),2)) "Rick Rothstein (MVP - VB)" wrote: Yes, that is true if there is a blank space in the row immediately in front of the last data item. Good catch. Here is a modification to what I posted that should work and is non-array entered... =OFFSET(A1,MATCH(LOOKUP(2,1/(INDIRECT("A1:A"&(MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-1))<""),A:A),A:A,0)-1,0) Yeah, it is longer than your array-entered offering, but it seems to work. Rick "T. Valko" wrote in message ... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) That will return incorrect results. Assuming there will be at least 2 non-empty cells. Array entered** : =INDEX(A:A,LARGE(IF(A1:A100<"",ROW(A1:A100)),2)) Since this is an array formula the inner range references can not be entire columns (unless you're using Excel 2007). ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... If your data in Column A has no gaps in it, this formula can be used... =OFFSET(A1,COUNTA(A:A)-2,0) If there are (or can be) gaps in your data, then this formula can be used... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) Rick "Luke" wrote in message ... This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
Yeah, that is a lot better than mine (I seem to have been "off" all day
today). Thanks. Rick "Teethless mama" wrote in message ... If you want non-array entered, then try this one =INDEX(A:A,LARGE(INDEX((A1:A100<"")*ROW(A1:A100), ),2)) "Rick Rothstein (MVP - VB)" wrote: Yes, that is true if there is a blank space in the row immediately in front of the last data item. Good catch. Here is a modification to what I posted that should work and is non-array entered... =OFFSET(A1,MATCH(LOOKUP(2,1/(INDIRECT("A1:A"&(MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-1))<""),A:A),A:A,0)-1,0) Yeah, it is longer than your array-entered offering, but it seems to work. Rick "T. Valko" wrote in message ... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) That will return incorrect results. Assuming there will be at least 2 non-empty cells. Array entered** : =INDEX(A:A,LARGE(IF(A1:A100<"",ROW(A1:A100)),2)) Since this is an array formula the inner range references can not be entire columns (unless you're using Excel 2007). ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Rick Rothstein (MVP - VB)" wrote in message ... If your data in Column A has no gaps in it, this formula can be used... =OFFSET(A1,COUNTA(A:A)-2,0) If there are (or can be) gaps in your data, then this formula can be used... =OFFSET(A1,MATCH(LOOKUP(2,1/(A1:A65535<""),A:A),A:A,0)-2,0) Rick "Luke" wrote in message ... This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Display next to last results
Maybe this:
=indirect("Sheet2!A"&count(Sheet2!a:a)-1) Just a guess... Regards, Ryan--- -- RyGuy "Luke" wrote: Marcello, the formula returned a blank cell. I think because it is looking for text and my data is numeric. However I do have blank cells in the column. Any thoughts? Luke "Marcelo" wrote: you can use =indirect("Sheet2!A"&counta(Sheet2!a:a)-1) hth -- regards from Brazil Thanks in advance for your feedback. Marcelo "Luke" escreveu: This formula works great to find the last entry in a col. =LOOKUP(99^99,Sheet1!A:A) Is there a way to get the formula to show the 2nd to the last entry? Thank you for all you do, Luke |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to insert a space between display results | Excel Worksheet Functions | |||
| Formula display - no results | Excel Discussion (Misc queries) | |||
| Display results of formulas | Excel Worksheet Functions | |||
| Display negative results | Excel Worksheet Functions | |||
| Select and Display top 5 results | Excel Worksheet Functions | |||
Linear Mode
