Reference to last cell

L

Lawman

In an expression, how do I refer to the last cell in a specified column that
is not blank?

Thanks in advance.

H
 
G

Gary''s Student

The value of the last number in column A is:

=LOOKUP(9.99999999999999E+307,A:A)
 
B

Bernard Liengme

=LOOKUP(99^99,Sheet1!H:H)
finds last non blank in column H of Sheet1
best wishes
 
P

Pete_UK

This will give you the last numeric value that is not blank:

=LOOKUP(10E10,A:A)

Is that what you want?

Pete
 
B

Bernard Liengme

My first answer works only with numbers

This works with text also but with no blank cell in the column before last
item
=INDIRECT(ADDRESS(COUNTA(D1:D500),4)) for column D
=INDIRECT(ADDRESS(COUNTA(K1:K500),11)) for column K

best wishes

Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email
 
L

Lawman

Thanks, it is exactly what I want. Weird though - and interestingly slightly
different from the other replies, which presumably also work.

I suppose I substitute 1:1 etc for last non-blank cell in a row.

H

This will give you the last numeric value that is not blank:

=LOOKUP(10E10,A:A)

Is that what you want?

Pete
 
P

Pete_UK

Glad to hear it worked for you. The first number in the LOOKUP is just a
large number (larger than you expect in your data), so it can be any value,
which explains the variations in the other responses - I think 10E10 is easy
to type and remember.

Yes, make it 1:1 to look in row 1 (make sure your formula isn't in that
row).

Pete
 
G

Gord Dibben

Do you want the value or the address?

Addresses..............

=ADDRESS(MATCH(99^99,A:A),1) of last number

=LOOKUP(REPT("z",255),A:A) of last text entry


Gord Dibben MS Excel MVP
 
Top