For instance, if he’s supposed to be typing in Zip Codes, and you want to make sure he doesn’t leave out digits or add extra ones, set the Text length validation rule to require a number between 5 and 5.If he’s supposed to enter 94607 and he types 9460 by mistake, he’ll receive an error message reading “The value you entered is not valid.”If you want to make sure he doesn’t forget to enter data in a critical cell, uncheck the “Ignore blank” box in the Data Validation dialog. (If you want, you can determine exactly what he needs to enter by selecting the appropriate entry in the Allow drop down.) If the “Ignore blank” box is grayed out, temporarily select anything except “Any value” in the Allow drop down; this makes the “Ignore blank” box accessible.The dollar signs in front of the column and row references ensure that those cell references won’t change if you copy the formula.If the cell references were written as C2 and so on, the reference would change if you copied the rule to another cell.
However, note that these special numbers change based on your computer’s local language settings.
Isn’t there some way to use a set of data from one worksheet as a source of validation criteria in another worksheet?
You can create a data validation criterion that uses a formula to check for previous entries of the same value in the column—but you must prepare the worksheet before you enter any actual data.
We don’t pay him a lot, but our deal is that he gets to learn about computers on our dime when he’s doing data entry.
There’s only one problem: he types so badly that he makes a ton of mistakes—adding extra numbers, leaving some out, even hitting letters instead of numbers.