Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Finding first blank cell

    I have been trying to set up a worksheet function to find the first blank cell
    in a range of 17 cells. The attached worksheet shows what I have tried and the
    results are very confusing. I had thought the MATCH function would work but it
    gives very erratic results.

    In the example,the formula marked red is whsat I am trying to do. The rest are just showing different result for the Match function. Also examples showingg thatg ISBLANK and IF seem to work ok.

    Thyer Offset formula seems to go one too far (Showing XXXX) when all cells are blank but seems to work (backwards) if a value is placed in Q3 or J3!!

    I had thought that the Match function worked from left to right but it appears to bw working right to left in this workbook.

    Can someone help me here???

    Thanks, Robbie
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about the array formula (confirm with ctrl-shift-enter):

    =MIN(IF(ISBLANK(A3:Q3),COLUMN(A3:Q3)))

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    ogm (2011-09-22)

  4. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Just for variation:
    =MATCH(TRUE,ISBLANK($A$3:$Q$3),0)
    array-entered, or:
    =MATCH(TRUE,INDEX(ISBLANK($A$3:$Q$3),0),0)
    entered normally.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #4
    Lounger
    Join Date
    Nov 2001
    Location
    Carson City, Nevada, USA
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thank you Steve and Rory, I will try these today and will let you know...

    ... Robbie

  6. #5
    New Lounger
    Join Date
    Jun 2012
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    just another variation for finding which address is it:
    {=ADDRESS(ROW(A1),COLUMN(INDEX(A1:Q1,1,MATCH(TRUE, LEN(A1:Q1)=0,0))),4)}

    Example of this is here: http://excel-example.com/other-tutor...-cell-in-a-row

Posting Permissions

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