Data Validation List from Another Workbook
You can use a list from another workbook as the source for a Data Validation
dropdown list 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 and later.
IMPORTANT: 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.
Create the Excel Data Validation Source
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
Create a Reference to the Source List
- 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
Create the Dropdown List
- 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,
- Click OK
Watch the Data Validation Video
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.
Create a Dynamic Range from Another Workbook
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
- 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
- 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.
Get All the Excel News
For regular Excel news, tips and videos, please sign up for the Contextures
Excel newsletter. Your email address will never be shared with anyone
Search Contextures Sites
Excel Tutorials - Data Validation
Data Validation Basics
Previously Used Items
Data Validation Tips