Data validation – List option (intermediate skill level)
- Data validation is a tool to control manual inputs into a cell. There are many options for this such as whole number, date etc but I find that the most useful is the list option. This restricts the input to a specific set of items such as names and is especially useful where the user wishes to ensure that something is spelled in a particular way so that it can be used elsewhere in the spreadsheet. In particular it will help protect against typos which we all make at some time or another!
- The source can be defined as a standard range or as a range name – see examples below.
- I have experienced many examples where the main systems of a business spell a particular item in different ways (e.g. region, product, division) as do other spreadsheets that you may be using as a source of data. Almost always your spreadsheet will have a particular spelling to identify an item that you need to use consistently throughout the workbook – the Data Validation-List option is the way to guarantee this for manual entries.
Excel Monkey 