Data validation - intgers bigger than 0 and empty cell not allowed

**fibonacci1101** · 05-31-2013, 03:46 PM

Hello Everyone,

Here is my problem. I would like to validate data in cell using data validation so that it content has to be an integer bigger than 0 and cannot be an empty cell. I've tried to use option "whole numbers" but then if I leave the cell empty there is no message that data is invalid. If I use option "nonstandard" and type =AND(A1>=0;A1<>"") I don't know how to include that is has to be an integer. I've tried to find functions like INT, INTEGER or ISINTEGER but it seems that there aren't any of those.

Could you please help me with my problem? I would be very grateful

**ChemistB** · 05-31-2013, 03:52 PM

Whole numbers greater than 0 will do what you are asking for (remove the checkmark next to "ignore blank" also),
HOWEVER,
data validation only works when you are entering something into a cell. It won't register that there is no value there. With the "ignore blank" checkmark removed, if you had a number and then erased it, you would get a warning error because you are changing what is in the cell.
The only way to check to see if the cell is still blank at some point (i.e. before closing or switching to a different spreadsheet) is to use VBA.

**Tony Valko** · 05-31-2013, 04:00 PM

Data validation won't work to prevent an empty cell.

You could use validation to allow only whole numbers greater than 0 and maybe put a formula in an adjacent cell with a message that clears once a valid entry is made in the other cell.

To apply validation for whole numbers >0...

Data Validation
Allow: Custom
Formula: =AND(A1>0,INT(A1)=A1)

Use the appropriate cell reference.

You could put a formula like this in cell B1:

=IF(AND(A1>0,INT(N(A1))=A1),"","Enter a whole number >0 in cell A1")

**fibonacci1101** · 05-31-2013, 04:05 PM

I've tried that. Initially I have zeros in the cells, than I apply data validation to those cells using option "whole numbers" and settings "bigger or equal to 0" and I uncheck ignore blank. Hit OK, and then if I delete one of those zeros nothing happens. There is no message. But if I try to enter there negative numbers, real numebrs or text there is a message. It looks like Excel treats "0" and "" as it is the same.

**ChemistB** · 05-31-2013, 04:16 PM

You initially said it had to be an integer greater than 0, not greater than or equal to. You are right, Excel won't differentiate in this case.

You can go with custom and
=AND(A1>=0,INT(A1)=A1, ISNUMBER(A1))