Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

EXCEL question -- last entry in column


(!)

KJS2's Avatar
KJS2 KJS2 is offline
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2005
Experience: Intermediate
27-Jan-2005, 03:13 PM #1
Question EXCEL question -- last entry in column
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's Avatar
maxflia10 maxflia10 is offline
Member with 331 posts.
 
Join Date: Feb 2003
27-Jan-2005, 08:29 PM #2
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's Avatar
KJS2 KJS2 is offline
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 10:36 AM #3
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's Avatar
KJS2 KJS2 is offline
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 02:51 PM #4
Thumbs up Solved - Excell last entry
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's Avatar
KJS2 KJS2 is offline
Junior Member with 4 posts.
THREAD STARTER
 
Join Date: Jan 2005
Experience: Intermediate
28-Jan-2005, 02:57 PM #5
p.s. I will make a donation to the site -- saved me many hours!!!!
maxflia10's Avatar
maxflia10 maxflia10 is offline
Member with 331 posts.
 
Join Date: Feb 2003
29-Jan-2005, 12:42 AM #6
Quote:
Originally Posted by KJS2

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.

Last edited by maxflia10; 29-Jan-2005 at 12:52 AM..
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2