Results 1 to 15 of 15
  1. #1
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I have a spreadsheet that I send out and it is returned to me filled in. The filled-in values are used then in subsequent calculations on that same spreadsheet.

    I've just realized that if one of the users neglects to fill in a cell, Excel treats it like a zero, and I have no way to know it was not filled in unless I happen to see it.

    Is there a way to have Excel give an error (or some other heads-up message) for unfilled-in cells?
    I don't want the error message to appear in the cell that the users fill in, I would like the error to appear in the subsequent calculations that the filled-in values are used in.

    Thanks

  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
    how about something like this
    =if(isblank(the cell/s in question),"The error msg you want",the formula that should be calculated if everything is okay)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Assume your data is transferred to Sheet1. Try this on Sheet1 ....

    =IF(Sheet2!C5=0,"Unfilled",Sheet2!C5)

    Sheet2! is the sheet being tested.
    C5 is the cell on Sheet2! being tested.

  4. #4
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    OK, thanks to both of you.

    I now have come up with a couple of different ways to solve this, based on what you've both written.

    My question now is, is there any difference between using "IF(isblank(A1)" or "IF(A1=0)" ?
    Seems like Excel treats a blank and a zero the same way, at least in my tests.

    Thanks

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    isblank distinguishes between a cell containing zero and an empty cell.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Vincenzo View Post
    I have a spreadsheet that I send out and it is returned to me filled in. The filled-in values are used then in subsequent calculations on that same spreadsheet.

    I've just realized that if one of the users neglects to fill in a cell, Excel treats it like a zero, and I have no way to know it was not filled in unless I happen to see it.

    Is there a way to have Excel give an error (or some other heads-up message) for unfilled-in cells?
    I don't want the error message to appear in the cell that the users fill in, I would like the error to appear in the subsequent calculations that the filled-in values are used in.

    Thanks
    Could you use conditional formatting on the cells that need to be filled in so that they are shaded if not filled?

  7. #7
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Vincenzo View Post
    My question now is, is there any difference between using "IF(isblank(A1)" or "IF(A1=0)" ?
    Seems like Excel treats a blank and a zero the same way, at least in my tests.
    "IF(isblank(A1)" is true if A1 is blank. It is false if A1 is a zero.
    "IF(A1=0)" is true if A1 is blank or a zero.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    [attachment=88930:Book1.xlsx] Good catch Gfamily, Yes he could - again using the isblank function.
    Attached Files Attached Files
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    Thanks for clearing up the distinction between the two functions.

    And thanks Gfamily for the idea.

    Vince

  10. #10
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    I am now re-doing this spreadsheet so I thought I'd try the conditional formatting as suggested in your example. When I try to reproduce it using the formula =ISBLANK(A1), Excel puts quotes around the formula and it is not working until I go back and edit the formula to remove the quotes.

    I am doing it in Excel 2007 by going to Conditional formatting/Highlight cells rules/more rules>Use a formula to determine which cells to format.

    What am I doing wrong here?

    Thanks

  11. #11
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Vincent - Try using Cell Value Is Equal to 0 <<< that is a zero
    Be sure to use a Pattern for color not a Font color
    .
    Tim

  12. #12
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    That does work, thanks.

    But I would also like to find out why the formula that I am entering ends up being in quotes and not working. I suspect I am entering it wrong somehow.

  13. #13
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Quote Originally Posted by Vincenzo View Post
    But I would also like to find out why the formula that I am entering ends up being in quotes and not working. I suspect I am entering it wrong somehow.

    Hello Vince - Make sure you are typing the equal (=) sign and the parenthesis ( ) when entering the formula.
    Formula Is =isblank(a1)

  14. #14
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by Vincenzo View Post
    That does work, thanks.

    But I would also like to find out why the formula that I am entering ends up being in quotes and not working. I suspect I am entering it wrong somehow.
    Are you including the '=' sign when entering =ISBLANK(A1) ?

  15. #15
    5 Star Lounger Vincenzo's Avatar
    Join Date
    Mar 2004
    Posts
    654
    Thanks
    95
    Thanked 14 Times in 13 Posts
    Yes I have been entering the = sign and the ( ).

    But interestingly, I needed to restore a Ghost backup image of the C: drive, for an unrelated issue, and since then this function is working correctly.

    Thanks for the help.

    Vince

Posting Permissions

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