Last entry in column

  • Thread starter Thread starter davegb
  • Start date Start date
D

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!
 
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?
Click to expand...

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
 
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
Click to expand...

The above line should read...

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

Rick
 
Back
Top