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

Thanks in advance.

H

The value of the last number in column A is:

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

--
Gary''s Student - gsnu200775


"Lawman" wrote:

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

Thanks in advance.

H

=LOOKUP(99^99,Sheet1!H:H)
finds last non blank in column H of Sheet1
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

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

Thanks in advance.

H

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

=LOOKUP(10E10,A:A)

Is that what you want?

Pete

On Mar 25, 1:48*pm, "Lawman" wrote:
In an expression, how do I refer to the last cell in a specified column that
is not blank?

Thanks in advance.

H

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

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

Thanks in advance.

H

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

"Pete_UK" wrote in message
...
This will give you the last numeric value that is not blank:

=LOOKUP(10E10,A:A)

Is that what you want?

Pete

On Mar 25, 1:48 pm, "Lawman" wrote:
In an expression, how do I refer to the last cell in a specified column
that
is not blank?

Thanks in advance.

H

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

"Lawman" wrote in message
...
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

"Pete_UK" wrote in message
...
This will give you the last numeric value that is not blank:

=LOOKUP(10E10,A:A)

Is that what you want?

Pete

On Mar 25, 1:48 pm, "Lawman" wrote:
In an expression, how do I refer to the last cell in a specified column
that
is not blank?

Thanks in advance.

H

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

On Tue, 25 Mar 2008 14:17:20 -0000, "Lawman" wrote:

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

"Pete_UK" wrote in message
...
This will give you the last numeric value that is not blank:

=LOOKUP(10E10,A:A)

Is that what you want?

Pete

On Mar 25, 1:48 pm, "Lawman" wrote:
In an expression, how do I refer to the last cell in a specified column
that
is not blank?

Thanks in advance.

H