To specify data validation for a cell or range of cells:
Select the cell or cells you want to apply data validation to
Select Validation from the Data menu
Select Validation Criteria
Using the Allow drop down, select the type of data you want to apply the validation to. Choose a data type and the remaining options in the dialog will change according to the type:
Any Value, the default, is the same as no validation. This can be useful if you want to display a message when a user moves into the cell but have no requirement to restrict the actual data entered.
Whole Number, Decimal, Data, Time all allow you to set a validation criteria where the value entered by the user must be in between, equal to, greater than etc a number or pair of numbers you specify in this dialog.
List – enter a list of numbers separated by semi-colons. For example, yellow,red,green will force the user to enter one these three colors. Check the In drop-down cell to have the list appear as a drop-down box so the user doesn’t have to type in the entry.
Text Length – specify a number or pair of numbers in the same way as for Whole Number. Text Length refers to the number of characters the user can type.
Note that cell references and ranges can be used to set criteria. For example, using a List criteria, you can specify:
Yellow,Red,Blue
Or
A1..A3 (where A1 contains Yellow, A2 contains Red etc)
Another example, set Whole Number and Greater Than and then set =A10 in minimum. Now, whatever value is in A10 will be used when validating the cell input.
Normally, blank entries are not allowed when data validation is turned on. Check the Ignore Blanks to override this.