Results 1 to 15 of 15

Thread: isblank (2003)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    isblank (2003)

    I can't figure out how to use a range in an isblank formula 'cause I'm stupid - help, please.

    In Cell J2 need a formula that looks at cells D2:I2 and if ANY cell is blank, return a message "check for data", otherwise return "".

    Thanks again

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    does this do what you want:

    <pre>=IF(COUNTBLANK(D2:I2)>0,"Check for data","")
    </pre>

    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Of course - it's you responding.

    Thanks again

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Ooops - spoke too soon - nope, even if one of the cells in the range has data in it, the "check for data" phrase appears.

    SORRY!!!

    Linda

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    You said you wanted the message if any cell in the range is blank. That is what the formula does. Do you want the message only if ALL the cells in the range are blank? if so, then use this formula:

    <pre>=IF(COUNTBLANK(D2:I2)=6,"","Check for data")
    </pre>

    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    This is what happens . . .

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    I don't understand. First, you put my formula in row 3 and it refers to D2:I2 (row 2). Second, it is doing exactly what your first message said you wanted to do. Five of the six cells D2:I2 are blank, therefore at least one of them is blank and you get the message. If you only want the message if all of the cells D2:I2 are blank, then you need to use the formula in my second message, not the one from the first message.
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: isblank (2003)

    The formula you have in J2 is incorrect.
    The formula in J3 (the one from Legare) works correctly for row 2 (since it refers to D2:I2): if ALL of the cells D2:I2 are filled, J3 is blank, but if ANY of the cells D2:I2 is blank, J3 says "Check for data".
    If that is not what happens for you, please explain.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    It works now - my niece helped me - the "" should have followed the "check for data" in the formula - now is works great.

    Thanks for the help and have a great day.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Hey Hans - see my last post to Legare

    Thanks for helping . . .

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    I'm back - it seems what I might actually need is to know how to use conditional formatting to format a cell that is blank but should have data in it. Or - maybe data validation - if the cell is blank I need a sock in the eye visually to tell me there should be data in this cell. Is there a way to do this?

    Thanks again

    Linda

  12. #12
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Yes. However, that is about the best answer I can give without more details.
    Legare Coleman

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Same spreadsheet as B4 - I have to do a sheet like this for 41 school sites, most of which have in excess of 100 rooms. I want a "prod" of some sort if I miss that, for example, I've neglected to put the square footage of a specific room in, or a room number, etc.

    Thanks again for all your help

    Linda

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Does the attached do what you want?
    Legare Coleman

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Fontana, California, USA
    Posts
    625
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: isblank (2003)

    Yes - thank you.

Posting Permissions

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