Last entry in column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

davegb - 23 Oct 2007 15:15 GMT

I've googled this forum, but can't find what I'm looking for. Probably
just not using the right terms. I want to show in a cell what the last
cell in a column with data in it is. I have a worksheet with a column
in which I occasionally add another entry for a VLOOKUP. I want to
automatically have the range for the VLOOKUP updated when I add
another item to the VLOOKUP list. So is there a way, other than VBA
code, to get the last cell address for my INDIRECT VLOOKUP to use?

Thanks in advance!

Reply to this Message

Ron Coderre - 23 Oct 2007 15:22 GMT

I believe a Dynamic Range Name is what you need. It automatically expands/contracts to accommodate the data.

See this site for instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> I've googled this forum, but can't find what I'm looking for. Probably
> just not using the right terms. I want to show in a cell what the last
[quoted text clipped - 5 lines]
>
> Thanks in advance!

Reply to this Message

Peo Sjoblom - 23 Oct 2007 15:27 GMT

http://www.contextures.com/xlNames01.html#Dynamic

Signature

Regards,

Peo Sjoblom

Reply to this Message

davegb - 23 Oct 2007 16:47 GMT

> http://www.contextures.com/xlNames01.html#Dynamic
>
[quoted text clipped - 15 lines]
>
> - Show quoted text -

Thanks to both of you. It worked great!

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Oct 2007 16:23 GMT

> I've googled this forum, but can't find what I'm looking for. Probably
> just not using the right terms. I want to show in a cell what the last
[quoted text clipped - 3 lines]
> another item to the VLOOKUP list. So is there a way, other than VBA
> code, to get the last cell address for my INDIRECT VLOOKUP to use?

This formula

="A"&SUMPRODUCT(MAX(ROW(A1:A65535)*(A1:A65535<>"")))

will return the address, as text, of the last column with data in it, so you
should be able to remove the equal sign and concatenate the remainder of it
into the text you are using in your INDIRECT VLOOKUP formula. Just as an
aside, if you wanted the contents of the last filled in cell in Column A,
this formula would give you that...

=INDIRECT("A"&SUMPRODUCT(MAX(ROW(A1:A65535)*(A1:A65535<>""))))

Rick

Reply to this Message

Rick Rothstein (MVP - VB) - 23 Oct 2007 16:28 GMT

I misstated something in my description for the formula I posted... see the
corrected inline description below.

>> I've googled this forum, but can't find what I'm looking for. Probably
>> just not using the right terms. I want to show in a cell what the last
[quoted text clipped - 10 lines]
> will return the address, as text, of the last column with data in it, so
> you

The above line should read...

"will return the address, as text, of the last **row** in Column A with data
in it..."

Rick

> should be able to remove the equal sign and concatenate the remainder of
> it into the text you are using in your INDIRECT VLOOKUP formula. Just as
[quoted text clipped - 4 lines]
>
> Rick

Reply to this Message

Rate this thread: