This is a discussion on Prevent null values in Excel data entry? within the Excel Questions forums, part of the Question Forums category; A funny thing happened on the way to the validation formula... I have a custom validation rule to prevent a ...
A funny thing happened on the way to the validation formula...
I have a custom validation rule to prevent a bunch of data entry cases, one of which is leaving a cell in a new record blank or deleting the cell value in an existing record. The null value assessment is ignored. Now, I understand doing nothing to a cell wouldn't provoke a validation rule, but apparently selecting a cell or deleting its contents won't either. Here's what I mean...
Starting with an empty cell, and using a formula to verify it's null indeed (a1="", isblank(a1), countblank(a1)...however you want to do it), I use a validation rule saying a null value is false.
I've already said the rule will not evaluate the cell until something is entered into it.
Selecting the cell and hitting Return won't work.
Deleting the cell's contents won't work.
But, double-clicking the cell or clicking in the formula bar as if to enter data, then hitting return will provoke the rule.
Of course, pasting a null value from elsewhere will delete the validation rule for the cell because the copied cell has no such rule. And, pasting a null value using paste-special-value is a handy way to circumvent any validation rule.
I'm posting this for two reasons:
1. Anyone else, without skills or time to make a form using VBA or XML or whatever, trying to find a way to force non-null data entry might find this information useful. I couldn't find anything about it, and I didn't think it was entirely obvious. Best I could design is, I put a comment on the cell and applied conditional formatting that highlights it if it's null.
2. It puzzles me that deleting the cell's contents does not provoke the validation rule, so I'm curious if anyone can explain that.