Search Contextures Sites
Excel Data Validation List from Another Workbook
Create the Excel Data Validation Source List
Create a Reference to the Source List
Create the Dropdown List
Create a Dynamic Range from Another Workbook
You can use a list from another workbook as the source for a Data Validation dropdown list.
For data validation to work, the workbook which contains the list must be open, in the same instance of Excel, when you use the drop down lists. You could create the list in a workbook that is always open, but hidden, such as the Personal.xls workbook.
The following instructions are for Excel 2003 and earlier versions. On the Contextures blog there are instructions for using a list from another workbook in Excel 2007.
The following instructions assume you have a workbook named DataValWb.xls, which contains a range named CustName.
For instructions on creating a named range, refer to Naming Ranges .
- Open the workbook that contains the source list -- DataValWb.xls in this example.
- Open the workbook in which you wish to use the list in Data Validation.
- Choose Insert>Name>Define
- Type a name for the List, e.g. MyList
- In the refers to box, type a reference to the named range. Start with an equal sign, then the workbook name and an exclamation mark, followed by the range name, e.g. =DataValWb.xls!CustName
- Click OK
- Select the cells in which data validation will be set.
- Choose Data>Validation
- In the Allow box, choose List
- In the Source box, type the list name, preceded by an equal sign, e.g.: =MyList
- Click OK
To see the steps for creating the data validation drop down from a list in another workbook, you can watch this short Excel video tutorial.
You can create a dynamic range that refers to a dynamic range in another (open) workbook.
- Create and save a workbook (MyLists.xls, in this example)
- Enter a list of names in cells A1:A10 on Sheet 1.
- To create a dynamic range, choose Insert|Name|Define
Use Employees as the range name, and the following formula:
- Keep MyLists.xls open, and create and save a new workbook (Schedule.xls)
- In Schedule.xls, create a range named EmployeeList with this formula:
- In cell A1 of sheet1, enter the following formula:
- Copy the formula down to row 200 (or any row beyond the length of the dynamic range in MyList.xls). Note: many of the rows will contain a #VALUE! error.
- In Schedule.xls, create another range, with the name NoErrors, and the formula:
(all one line)
- Use NoErrors as the source for your Data Validation list.
Use the Data Validation List
To use the data validation drop down lists, both workbooks must be open.
- Open both workbooks -- the one that contains the drop down lists, and the workbook that contains the original source list.
- Select an item from the data validation drop down list.
Excel Tutorials - Data Validation
Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List
Data Validation - Dependent Lists With INDEX
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items
Contextures Inc., Copyright ©2013
All rights reserved.