Last entry in column

[davegb]

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!

 

[Ron Coderre]

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)

 

[Peo Sjoblom]

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


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

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?

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

 

[Rick Rothstein \(MVP - VB\)]

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

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

The above line should read...

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

Rick

 

[davegb]

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

--

Regards,

Peo Sjoblom







- Show quoted text -

Thanks to both of you. It worked great!