# EXCEL question -- last entry in column

Discussion in 'Business Applications' started by KJS2, Jan 27, 2005.

Not open for further replies.
1. ### KJS2Thread Starter

Joined:
Jan 27, 2005
Messages:
4
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!!!!

2. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331
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))

3. ### KJS2Thread Starter

Joined:
Jan 27, 2005
Messages:
4
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!

4. ### KJS2Thread Starter

Joined:
Jan 27, 2005
Messages:
4
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.

5. ### KJS2Thread Starter

Joined:
Jan 27, 2005
Messages:
4
p.s. I will make a donation to the site -- saved me many hours!!!!

6. ### maxflia10

Joined:
Feb 24, 2003
Messages:
331

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.

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!