Data Validation

The main purpose of data validation is to create restrictions on the type of data that can be entered in a cell.
Very useful for preventing the entry of incorrect data or data that does not meet a particular criteria.
Data Validation only restricts the data that is entered - the cells can still be deleted or pasted over.
Data Validation is a way to validate your data without doing any actual programming.

It is possible to copy and paste invalid data into cells with data validation ??
In Excel 2007 it became possible to reference cells that are not on the same worksheet

What can you use Data Validation for ?

1) For restricting the type of data that can be entered in a particular cell.
2) To provide a drop-down list of values that can be entered in a particular cell.
3) Displaying a descriptive prompt to remind users what type of information should be entered in a cell.
4) Displaying an error message if the user enters an invalid value in a cell.
5) Prevent a value appearing more than once.
6) numbers within a specific range.
7) a certain number of characters to be entered.
8) restricts entries to a specific type or size.

Using Data Validation

This can be found by selecting (Data > Validation).
Data Validation can be very useful for setting up worksheets which will be used by other users.
This allows you to specify conditions which must be meet for the input data to be accepted.

Once data has been defined for a particular cell(s) it is not possible to link the cell to another cell that contains invalid data.

Cell References
If you use any relative cell references in your criteria then these will be automatically shoft to reflect the active cell.
To guarantee that you always get the expected results you should always try and use absolute cell references.

Controlling the height of the Drop-Down list

You can only have a source reference to cells on the active sheet, although if you want to refer to a range on a different worksheet then you can use a named range.

You can just type the entries directly into the source box as a comma separated list or use a named range.

To view cells that contain validation rules select (Edit > GoTo), click the special button and select the Data Validation button. Any cells with rules will appear as a non contiguous selection. Use the Tab key to cycle through all cells.

Does not allow references to other worksheets or workbooks

You can use this to set up cells and references that only accept entries of a certain type or within a certain value range. There is an option on the paste special to paste Validation Rules.

You can restrict cell entries to a predefined list. (Data > Validation). In the allow box, select list. Make sure the "in cell" drop-down is selected.

There are three levels of data validation Information, Warning and Stop. The default is Stop which means that data or numbers cannot be entered outside the validation rules. If you use the Warning or Information levels then the last two buttons on the auditing toolbar will add and clear circles around any invalid entries

Using Data Form

Validation checks are not carried out if you are using Data Form

Changing the Font Size

The font size cannot be changed

Changing the number of items displayed

You cannot change the length of the list. It will always display 8 entries.

If you reduce/increase the zoom what happens to the drop-down ??

Important

If you copy a cell and paste it over a cell that has Data Validation applied to it then the validation will be lost without a prompt.
You can copy data validation from one cell to another by using the (Edit > Copy) and (Edit > Paste Special) dialog box.
If you want to apply data validation to a large block of cells, it is easier to add the data validation to just one cell and then copy and paste the data validation using the Paste Special dialog box.
These rules only apply to data that is entered into a cell manually and does not work on data that is pasted into cells using a macro ??