Drop Down List from Another Workbook
In Microsoft Excel, you can create a drop down Data Validation list, so it’s easy to enter valid items in a cell. Usually, the list of valid items is stored in the same workbook, but it’s possible to set up a drop down from a list in another workbook. NOTE: Both workbooks must be open, any time you want to use the drop down lists.
A master list will be created in one workbook, and drop down lists in a different workbook will be based on that list. You can try these steps in your own workbooks, or download the sample files, with the dropdowns, for this tutorial.
IMPORTANT: For the drop down list 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 workbook.
Video: Drop Down List from Another Workbook
To see the steps for creating the data validation drop down from
a list in another workbook, watch this short Excel video tutorial. The written steps are below the video.
Create Workbook With Master
The first step is to create a master workbook, and a master list of items, in that workbook. In this example:
- The master list in a workbook named DataValWb.xlsx
- The master list contains customer names, in a range named CustName
Tip: For instructions on creating a named range, go to the Naming
If you are using your own file with a master list, substitute your master file's name and list name in the instructions below.
Create a Reference to Master List
Next, follow the steps below, to create a named range in the workbook where the data entry drop down list will be added.
- To start, make sure the master workbook is still open — DataValWb.xlsx in this example.
- Next, open the workbook in which you wish to use the list in Data Validation
- Or, create a new workbook for the drop down lists.
- On the Excel Ribbon, click the Formulas tab
- Next, click the Define Name command
Define Name for Master List
When the New Name dialog box opens, follow these steps:
- Type a name for the List, e.g. MyCustList
- From the Scope drop down, select Workbook
- Click in the Refers to box — this is where you’ll type a reference to the named range in the source workbook
- Type an equal sign, then the source workbook name and extension. Do NOT include any square brackets in the name. For example:
- OR, if the workbook name includes spaces, start and end the name with an apostrophe. For example: ‘DataVal May10.xlsx’
- Next, type an exclamation mark — !
- Finally, type the range name in the source workbook
- The completed reference should look like this:
OR =’DataVal May10.xlsx’!CustName
Create the Drop Down List
Next, you can create one or more drop down lists, based on the name that you just created.
- Select the cells where you want the drop down lists.
- On the Ribbon, click the Data tab
- In the Data Tools group, click the Data Validation command
- In the Data Validation dialog box, go to the Settings tab
- Click in the Allow box
- In the drop down list of options, choose List
- Click in the Source box
- Press the F3 key, to open the Paste Name dialog box
- In the list of names, click on MyCustList
- Click OK, to close the Paste Name dialog box
- The selected name appears in the Souce box
- Click OK button to close the Data Validation dialog box.
Use the Drop Down List
To use the data validation drop down lists, both workbooks must
- First, open the workbook that contains the master list.
- Open the workbook that contains the drop down
- Select an item from one of the the data validation drop down lists.
Get the Sample Files
Get the zipped Drop Downs from Other Workbook sample
files. There two files and both are in xlsx format. The files do not contain macros.When you unzip the files, keep both files in the same folder. Open master file first (DataValWb.xls)
Drop Down List Basics
Previously Used Items
Data Validation Tips