return last value of a sheet

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

widman - 23 May 2007 20:59 GMT

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 to this Message

JLatham - 23 May 2007 21:14 GMT

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)

> 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 to this Message

widman - 23 May 2007 22:42 GMT

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?

> If all of the cells in column A on Sheet1 are filled, this would do it for
> you. In any cell on Sheet2:
[quoted text clipped - 6 lines]
> > sheet 1 until values were put into A21, then they would show the values from
> > A21, then A22, etc.

Reply to this Message

Peo Sjoblom - 23 May 2007 21:15 GMT

=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))

Signature

Regards,

Peo Sjoblom

> Is there a formula I can put on a worksheet that picks up the values from
> the
[quoted text clipped - 5 lines]
> from
> A21, then A22, etc.

Reply to this Message

widman - 24 May 2007 01:59 GMT

the lookup worked fine, thanks

> =LOOKUP(2,1/($A$1:$A$65535<>0),$A$1:$A$65535)
>
[quoted text clipped - 11 lines]
> > from
> > A21, then A22, etc.

Reply to this Message