Need Excel to show error for empty cells

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

isblank distinguishes between a cell containing zero and an empty cell.

Reply | Quote

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?

Reply | Quote

Good catch Gfamily, Yes he could – again using the isblank function.

Reply | Quote

Thanks for clearing up the distinction between the two functions.

And thanks Gfamily for the idea.

Vince

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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) ?

Reply | Quote

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

Reply | Quote