+ Reply to Thread
Results 1 to 6 of 6

find first non blank cell in a row

  1. #1
    Registered User
    Join Date
    10-24-2008
    Location
    California
    Posts
    4

    find first non blank cell in a row

    First time here. I'm new to excel, so this will probably be very simple for most of you, but I have been trying to find an answer for hours. Any help would be much appreciated!

    I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas by searching forums/google etc., but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

    The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!
    Last edited by NBVC; 10-24-2008 at 01:57 PM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Try:

    =INDEX(B9:FL9,MATCH(TRUE,B9:FL9>0,0))

    confirmed with CTRL+SHIFT+ENTER not just ENTER.

    You may need to format the result cell as Date.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    10-24-2008
    Location
    California
    Posts
    4
    Thanks. That is much closer than anything else I have found. However, it only works on a row with no blank cells. If there is a blank cell ("") before the most recent date, it returns a blank cell. If there are only zeros and dates, it returns the most recent date. Did I do something wrong?
    Last edited by mdmarsh; 10-24-2008 at 02:03 PM.

  4. #4
    Forum Contributor
    Join Date
    07-23-2006
    Posts
    185
    Try
    Please Login or Register  to view this content.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Quote Originally Posted by mdmarsh View Post
    Thanks. That is much closer than anything else I have found. However, it only works on a row with no blank cells. If there is a blank cell ("") before the most recent date, it returns a blank cell. If there are only zeros and dates, it returns the most recent date. Did I do something wrong?
    It should work as I have given....even with your described conditions.

    Did you confirm the formula with CTRL+SHIFT+ENTER?


    P.S. If you indicate that your thread is Solved, and then discover issues, it is better to add a post to your thread rather than edit your last post. This way, the thread is re-opened. I/we don't usually go back to review solved threads to see if additions or edits have been made.

  6. #6
    Registered User
    Join Date
    10-24-2008
    Location
    California
    Posts
    4
    Sorry about taking so long. I had to help my children w/some stuff. Thanks for the user tip as well as the excel help. The formula works perfectly. I really appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1