Permitting only correct data entry in tables(Excel XP, 2003, 2007)

            You have stored a worksheet centrally so that diff erent users can work with one Excel fi le. Unfortunately your colleagues do not follow your specifi cations and for instance for a positive statement alternatively use ‘N’, ‘n’, ‘NO’ or even ‘X’.

             By defining validation rules you can restrict an entry to particular, allowed values. For this, select the cells for which you want to define the validation rules and open ‘Data | Validation’. In Excel 2007 click on ‘Data Validation’ in the tab ‘Data’ of the multi function bar. In the subsequent dialog you can define the validation rules, write automatic help messages and define error alerts. You select the validation rules on the tab ‘Settings’, in the combination field ‘Allow’. The default ‘Any Value’ allows the user arbitrary entries. On the basis of your selection further fields appear with the help of which you can refine the rule. So the rule ‘Whole Number’ for example, allows via ‘Minimum’ and ‘Maximum’ the entry of a range in which the entered number must lie. The rule ‘List’ is particularly interesting, with which you can limit the entry to a particular number of possibilities. You list these in the field ‘Source’ and separate each entry with a semicolon.
            The problem described at the beginning can be avoided by defining the specification ‘Yes; No’ in ‘Source’. Also activate the option ‘In-cell dropdown’. Then Excel shows the user the allowed entries in a drop down fi eld. All rules can force the entries. For that remove the check in front of ‘Ignore blank cells’. Additionally you can activate the option ‘Show input message when cell is selected’ on the tab ‘Input Message’. The message, regardless of whether it is an explanation of the possible entries or the meaning of the information to be entered, can be described in the self descriptive fi elds ‘Title’ and ‘Input Message’. The message then appears as quick info as soon as someone selects the cell. Lastly, on the last tab of the dialog, ‘Error alert’, you can give a hint to the user, who does not follow the rules. This hint appears in a simple dialog which you can describe with ‘Style’, ‘Title’ and ‘Error Message’.
HINT: When you select the ‘Style’ as ‘Warning’ or ‘Information’, Excel offers the user the possibility to quit the hint with ‘Yes’ or ‘OK’, after which the spreadsheet still accepts the wrong entry.