Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
return last value of a sheet
Is there a formula I can put on a worksheet that picks up the values from the
last row entered in the previous sheet, changing as rows are added. In other words, this forumla in A1 of sheet 2 would show the value of A20 in sheet 1 until values were put into A21, then they would show the values from A21, then A22, etc. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
return last value of a sheet
If all of the cells in column A on Sheet1 are filled, this would do it for
you. In any cell on Sheet2: =OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0) "widman" wrote: Is there a formula I can put on a worksheet that picks up the values from the last row entered in the previous sheet, changing as rows are added. In other words, this forumla in A1 of sheet 2 would show the value of A20 in sheet 1 until values were put into A21, then they would show the values from A21, then A22, etc. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
return last value of a sheet
=LOOKUP(2,1/($A$1:$A$65535<0),$A$1:$A$65535)
if there can be no blank cells between last and first value you might use =INDEX(A:A,COUNTA(A:A)) -- Regards, Peo Sjoblom "widman" wrote in message ... Is there a formula I can put on a worksheet that picks up the values from the last row entered in the previous sheet, changing as rows are added. In other words, this forumla in A1 of sheet 2 would show the value of A20 in sheet 1 until values were put into A21, then they would show the values from A21, then A22, etc. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
return last value of a sheet
Started great, but for some reason as I copy it across the top of sheet 2
(which I will then use for a report) it stops after G. No matter what I put in H or beyond, even the same values as show up in G, H returns 0. Any ideas? "JLatham" wrote: If all of the cells in column A on Sheet1 are filled, this would do it for you. In any cell on Sheet2: =OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0) "widman" wrote: Is there a formula I can put on a worksheet that picks up the values from the last row entered in the previous sheet, changing as rows are added. In other words, this forumla in A1 of sheet 2 would show the value of A20 in sheet 1 until values were put into A21, then they would show the values from A21, then A22, etc. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
return last value of a sheet
the lookup worked fine, thanks
"Peo Sjoblom" wrote: =LOOKUP(2,1/($A$1:$A$65535<0),$A$1:$A$65535) if there can be no blank cells between last and first value you might use =INDEX(A:A,COUNTA(A:A)) -- Regards, Peo Sjoblom "widman" wrote in message ... Is there a formula I can put on a worksheet that picks up the values from the last row entered in the previous sheet, changing as rows are added. In other words, this forumla in A1 of sheet 2 would show the value of A20 in sheet 1 until values were put into A21, then they would show the values from A21, then A22, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search for value on another sheet, return Y/N | Excel Discussion (Misc queries) | |||
Return to sheet | Excel Worksheet Functions | |||
search for and return more than one row in a sheet | Excel Worksheet Functions | |||
return cell from named sheet | Excel Discussion (Misc queries) | |||
How can I cause a cell to return the sheet name i.e. sheet 1? | Excel Worksheet Functions |