Formula that uses Last Number in Column of another worksheet

G

Gladys

I'd like to create a formula that always uses the value of a formula which is
located in the last cell in a column for a calculation. Also to mention that
this is also stored in another worksheet.

Entries are made daily so I do not know what the last cell of the column
will be. This would help alot so that I do not have to manually change my
formula daily.
 
L

L. Howard Kittle

Try something like this.

=LOOKUP(99^99,Sheet2!A:A)

Returns the last number in column A of sheet 2.

HTH
Regards,
Howard
 
G

Gladys

Thanks very much - this works great -

One favor though - could you explain to me what the 99^99 portion of the
formula is doing?

Thanks again.....
 
L

L. Howard Kittle

Hi Gladys,

If the value to be looked up is greater than any of the values in the lookup
array, LOOKUP will return the last value in the array. So 99^99 is a huge
number, one not likely to be in the lookup array, and therefore will return
the last value in the column. If you knew for certain that any number in
the column would never exceed 100, you could use 101 instead of 99^99 and it
would work just the same.

Just to add, if you wanted the second to the last value in your column that
is changing in length you could use:

=LOOKUP(99^99,A:A)-1

HTH
Regards,
Howard
 
Top