custom data validation formula

[Yago87]

I'm using Excel custom data validation to restrict entry in D2, if D1 is 0 or blank. The formula =D1>0 works just fine if D1 is a number greater than 0, but if DI is blank, the restriction does not work. Help for this formula please?

 

[DRSteele]

Welcome to the forum.

Remove the checkmark in the box beside Ignore Blank in the validation criteria window.

 

[jtakw]

Posted again here: https://www.mrexcel.com/forum/excel-questions/1037371-custom-data-validation-formula.html

and here: https://www.mrexcel.com/forum/excel-questions/1037372-date-validation-formula.html

 

[Yago87]

Thanks for the suggestion but that didn't have any affect on the issue.

 

[DRSteele]

It does indeed have an effect (not affect). The problem is that when a DataValidation rule is created and applied, the applicable cell can still contain what you think is a disallowed value. Only when you try to change it later will the rule be invoked and affect the user's ability to enter a value.

I put -3 in D1. I created a Custom DV rule in D2 with =D1>0 and the Ignore Blank box un-checked. Then I tried to type something into D2 and the entry was blocked by the DV rule. I deleted the contents of D1, which made D1 blank; then I tried to type something into D2 and the entry was blocked by the DV rule. These are the expected behaviours: the DV rules only have effect when you try to enter values into the applicable cell - they do not affect existing entries.

 

[Yago87]

Thank you for taking the time to reply. I originally used the exact same logic you stated but was still having trouble. However, I now discovered that the problem is related to how a number is deleted in D1. If I delete the number in that cell by clicking "clear contents" for example, the rule works just fine. If I remove the number by simply pressing the space bar, then the rule does not work. Since this sheet will be used by employees of mine (who I suspect will use the space bar method), I need to find a very user-friendly way to allow them to use this sheet. Perhaps I need to format cell D1 in a different way, so that the DV rule has the proper effect regardless of how numbers are deleted?

 

[DRSteele]

Pressing the space bar means the cell is not blank - it has a space, which is a character. Instruct your workman to press the delete key instead.

 

[Marcelo Branco]

Maybe this formula in DV
=AND(ISNUMBER(D1),D1>0)

M.

 

[DRSteele]

It might help you and your team to watch some instructional videos about Excel. The ExcelIsFun channel on youtube is a depository of thousands of insightful ones. Mike just started a new series regarding Business Math that might help; here is the first in the series https://youtu.be/8TB3VNS2sTU

 

[Yago87]

Thanks again!