This note describes several tips about using validation with List settings in Excel. We discuss validating alphanumeric fields and the examples used are from the Student Data sheet in Validation example spreadsheet (XLS 58 KB).
From time to time people ask us questions about validating spreadsheet data, restricting it to a list, and we thought we would write about some of the issues here. As mentioned in our previous article we suggest that you read Theo Callahan‘s “Block that spreadsheet Error" for the basics as he has covered them really well.
Once again we’re going to use student data for our examples. We’ve included Home Towns in this example as that provides a situation where a separate list of usual home towns is useful for validating the data.
We shall also look at the issues of case sensitivity of lists.
Using a List on another sheet to validate data
In our Validation example spreadsheet (XLS 58 KB) we have a list of likely NZ Home Towns for our students. As you can see in the picture the NZ towns data has been range named HomeTowns. Assigning a range name to the list is essential if we are going to use it to validate data on another sheet. (A quick way to assign a range name is to select the required range, then type the range name (HomeTowns in this case) in the Name Box (top left) and press [enter].)
If a student is not from NZ or from a NZ town not on the list we must be able to enter his or her correct home town so we have to remember this when setting up the validation.
To set up the validation we first select the HomeTown column and then apply Data, Validation (Data | Data Validation in Excel 2007). Now comes the trick needed to get over problems with the interface. On the Settings tab when Allow, List is selected the Source is to be our list of home towns. Surprisingly, you cannot point to this list in the normal manner. Only lists on the current sheet can be selected. To get over this problem you need to use the range name set up earlier in a formula. You can type the formula =HomeTowns. An easier way to do this is use the Paste Name function key [F3] and select the name from the list. The formula will then be created for you. (This quick key is worth remembering as it is useful in many situations.)
If it is essential to be able to enter values other than those on the list (e.g. in our case some students may come from towns outside NZ), then the Error Alert tab should be changed from the default of Stop to Warning. Stop prevents other values from being entered whereas Warning doesn’t.
Once the list validation has been created then each cell to which it applies will have a useful pull-down list to assist with data entry. Correct values can still be typed in the cell.
With Warning as the Error Alert, if a value not on the list is typed in the active call, the user has the choice of whether or not they continue with the data entry.
Issues with Case Sensitivity
You have probably realised that spreadsheet features are generally not case sensitive. The formula =IF(C6="m", “bloke”,”not bloke”) will result in bloke regardless of whether C6 contains an m or an M. Similarly an Autofilter doesn’t differentiate between the two either.
Validation (in Excel) is a bit different. It is case sensitive. When List settings are used, with the Stop Error Alert, then if it is desirable to allow both upper and lower case data entry then the list needs to contain both. Our picture shows the validation setting for the Gender field. Although you can’t see it here we’ve also left the ignore blank option turned on. This is always a good strategy where you might have missing data. It is very impolite to assign someone the wrong gender and it is not always easy to guess from a First Name!