# EXCEL question -- last entry in column



## KJS2 (Jan 27, 2005)

For Excell 2000 -- In a column e.g A1 to A5 - how can I get cell A6 to return the last entry in the column? Logically the formula would check the cells starting w/ A5 for "Emptyness" and would keep going sequentialy until it got to a cell that had either a text or numerical entry.

cat 
dog 
3
Bingo

Bingo -- cell A6 would return last entry -- "Bingo"

or

cat
dog
3


3 -- cell A6 would return last entry or "3"

Column lengh can be any number of cells long but each cell is filled sequentialy - A1 then A2 then A3 etc

My only success (limted) is w/ nested "IF/THEN" statments and then I was limted to a column of 5 entries -- 

Any suggestions would be greatly appreciated!!!!


----------



## maxflia10 (Feb 25, 2003)

For mixed data,

=INDEX(A:A,MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",60),A:A)))

with the Morefunc addin

=LASTROW(A:A)

For numeric data only

=LOOKUP(9.9999999999999+307,A:A)

for text data,

=INDEX(A:A,MATCH(REPT("z",90),A:A))


----------



## KJS2 (Jan 27, 2005)

Thank You!!!! -- I got the # only to work and the text only to work -- and I can get the number potion of the combined to work -- still havn't puzzled out the text potion of the combined -- will work more on it this PM. 

Thanks again!


----------



## KJS2 (Jan 27, 2005)

:up: My problem was solved -- thank you MAXFLIA10 - whoever you are!!

A suggestion to any who might try to use this is to delete the 9.999999999E+307 and replace with a whole number that is greater than any number that may show up in the column -- in my case I used the # 10,000,000 -- and it workded great.

The only other problem I had is that all cells must be "clean" -- any cells "deleted" w/ the space bar will cause a "#N/A" to appear. As soon as I "cleaned them" w/ the right click "clear contents" the formula worked.

Thanks again.


----------



## KJS2 (Jan 27, 2005)

p.s. I will make a donation to the site -- saved me many hours!!!!


----------



## maxflia10 (Feb 25, 2003)

KJS2 said:


> A suggestion to any who might try to use this is to delete the 9.999999999E+307 and replace with a whole number that is greater than any number that may show up in the column -- in my case I used the # 10,000,000 -- and it workded great.
> 
> Thanks again.


You're short a few 9's,

=9.99999999999999E+307 is the biggest number Excel recognizes. You can also define the numbers and use the name in the formula.

=LOOKUP(NAME,A:A)

Same with the REPT("z",90)

BTW,

I'm glad I could help! 

I'm relatively new here and appreciate your comments.


----------

