When creating a validation list, it is often neater to store the source of the list on a separate worksheet, away from the main data.
In Excel 2007 and earlier, you can’t store the “list source” on a different sheet to where the list will be used, unless you name the range containing the “list source”. Any attempt to do so will result in an error. This has been fixed in Excel 2010
What is List Validation?
In the example above, C2 has “list validation” applied which means that C2 must contain one of the 5 country names listed in the drop-down. This screenshot is from a sheet called Sheet2
Creating the Validation List
The source of the validation list is A1:A5 in a sheet called Notes.
Applying the Validation
To apply the validation to C2 in Sheet2…
Select C2
Select Data > Validation (in version 2003 and earlier. In Excel 2007, select Validation from the Data tab on the Ribbon)
Select List from the Allow dropdown
Type =Notes!A1:A5 into the Source box
When you click OK a message will appear, advising that the source cannot refer to a range on a different worksheet.
Click Cancel
Defining a Name
Select the Notes sheet
Select A1:A5, type the word Countries into the Name Box and press Enter
The name Countries has been assigned to A1:A5
Applying the Validation
Select Sheet2
Select C2
Select Data > Validation (in version 2003 and earlier. In Excel 2007, select Validation from the Data tab on the Ribbon)
Select List from the Allow dropdown
Type =Countries into the Source box
Click OK
In Excel 2010 and later, you can type (or browse and select) =Notes!A1:A5 into the Source box and it works (i.e. you don’t need to name the range containing the list source)