Using Offset

  • Thread starter Thread starter scott
  • Start date Start date
S

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?
 
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
 
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
 
Back
Top