EXCEL question -- last entry in column

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

KJS2

Thread 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!!!!
 
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))
 

KJS2

Thread 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!
 

KJS2

Thread Starter
Joined
Jan 27, 2005
Messages
4
(y) 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

Thread Starter
Joined
Jan 27, 2005
Messages
4
p.s. I will make a donation to the site -- saved me many hours!!!!
 
Joined
Feb 24, 2003
Messages
331
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! :D

I'm relatively new here and appreciate your comments.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
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.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top