How to get the last entry in a column.

Posted by Steve on November 21, 2000 8:51 AM

I have a column with five numbers in it (they correspond to a date) and I'm trying to get the last number in the column. I have tried the max fucntion but it's possible that the last number might not be the highest also the number of entries is dynamic.

Posted by marbel on November 21, 2000 10:05 AM

How about adding a "counter" column along the left edge of your table, and using a vlookup as follows:
=VLOOKUP(MAX(A1:A13),A1:C12,3,FALSE)

where column A is your counter column and column C (3rd one over, 3 in the formula) is the value to be returned.

You could get tricky and cover yourself for changing table size by putting in more counters than you'll ever use, with the counter cells starting in A1 with a 1, and in A2 through the end of your possible table size using:
=IF(B2="","",A1+1)
Then fill to the end of your biggest possible table.

There are prettier ways to deal with your changing table size with macros, about which Mr. Excel has written extensively.

Posted by Steve on November 21, 2000 12:03 PM

Thanks for the help. Instead of adding another column I justed set the max on the date field and it worked. Thanks again.

Posted by Steve on November 21, 2000 12:07 PM

Thanks for the help. Instead of adding another column I justed set the max on the date field and it worked. Thanks again.