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