Using Offset

[scott]

if i have A1 through A5 containing 5,8,8,3,2 and add a new value each day
going straight down like A6 = 7 for tomorrow, if i want cell B1 to return
the value of the last cell in column A, how can i get OFFSET to find the
last value entered in column A?

 

[Gord Dibben]

Scott

You can do it without using OFFSET if you wish.

If values are numeric per your example.........

=MATCH(9.99999999999999E+307,A:A) returns row number of last numeric cell in
column A

=ADDRESS(MATCH(9.99999999999999E+307,A:A),1) returns address of last numeric
cell in column A

=LOOKUP(9.99999999999999E+307,A:A) returns the last value in column A

Gord Dibben Excel MVP

 

[Peo Sjoblom]

One way

=OFFSET($A$1,COUNTA(A:A)-1,)

 

[Stefan Hägglund [MSFT]]

Hi Scott!

You could use the following formula in B1:
=OFFSET(A1;COUNT(A1:A100)-1;0)

The function COUNT is used to count how many cells that are used, so you
cannot enter a value/text below the data in this column inside the area
A1:A100.


Stefan Hägglund
Microsoft