Results 1 to 8 of 8
  1. #1
    Steven B.
    Guest

    Find Blank Cell location in Range

    I've set up a checklist of components for custom computers, with a columnar range where the person enters the quantity desired. There should be only one item entered in the range. What I would like to do is find which cell in the range is the nonblank cell and have the description in the cell to the right of the filled cell appear in a different area of the sheet. Any ideas on how to do this?

    Thanks,
    Steve

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Find Blank Cell location in Range

    Mmm
    A bunch of answers here.
    If you use goto special you can go to blank cells, but when I read you question again I see you aren't looking for blanks.
    It sound like a vlookup formula is called for here - but I don't know if your sheet is set up to take advantage of one.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find Blank Cell location in Range

    The formula =OFFSET(B1,(MATCH(0,A1:A100,-1)-1),0) should work if the item you are looking for is a positive number, and is the first number in the column.

    The formula assumes the number is in column A and the description is in column B. It also assumes that the data does not extend beyond row 100. All of these can be changed to suit your requirements. In this example replace A1:A100 with A:A to include the entire column A.

    Hope it is what you want,

    Andrew C

  4. #4
    Steven B.
    Guest

    Re: Find Blank Cell location in Range

    I think I will end up with a lookup table, but first I need to identify exactly which cell has been choosen in the range. It seems there should be something like ISBLANK=FALSE for an array with CELL to give that location. Then I could use the description in the cell to the right for a lookup criteria.

  5. #5
    Steven B.
    Guest

    Re: Find Blank Cell location in Range

    Your answer seems to be the solution. The only part I can't get to work is using the CELL command to give me the upper left corner of the range, which I assume is because CELL returns a text value which need to be converted to a true reference. Is there a simple way to do this? The CELL expression below works fine outside of the OFFSET formula.

    =OFFSET(CELL("address",AMDQty),(MATCH(1,AMDQty,-1)-1),1)

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Blank Cell location in Range

    I'm not sure why, but it will work if you put the CELL() function inside of the INDIRECT function, as follows:

    =OFFSET(INDIRECT(CELL("address",AMDQty)),(MATCH(1, AMDQty,-1)-1),1)

    JIM

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Find Blank Cell location in Range

    Steve,

    The CELL() function fails because, as you rightly assumed, it returns a text string. The INDIRECT() function takes a text string, and returns a reference (assuming the string is suitable), hence an indirect way of referring to a cell.

    Jim has provided the formula which works because the combination of INDIRECT() and CELL() provide the reference (in this case the first cell in the range AMTQty) that OFFSET requires.

    Andrew

  8. #8
    Steven B.
    Guest

    Re: Find Blank Cell location in Range

    Thank You , problem solved.

    Steve

Posting Permissions

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