1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL question -- last entry in column

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

Thread Status:
Not open for further replies.
Advertisement
  1. KJS2

    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!!!!
     
  2. maxflia10

    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. KJS2

    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!
     
  4. KJS2

    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.
     
  5. KJS2

    KJS2 Thread Starter

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

    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! :D

    I'm relatively new here and appreciate your comments.
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/324036