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.

Introduction

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 List

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 Ranges page.

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:
  • =DataValWb.xlsx
  • 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:
  =DataValWb.xlsx!CustName
  OR =’DataVal May10.xlsx’!CustName
  

• Click OK

Create the Drop Down List

Next, you can create one or more drop down lists, based on the name that you just created.

1. Select the cells where you want the drop down lists.
2. On the Ribbon, click the Data tab
3. In the Data Tools group, click the Data Validation command
4. In the Data Validation dialog box, go to the Settings tab
5. Click in the Allow box
6. In the drop down list of options, choose List
7. Click in the Source box
8. Press the F3 key, to open the Paste Name dialog box
9. In the list of names, click on MyCustList
10. Click OK, to close the Paste Name dialog box
11. The selected name appears in the Souce box
12. 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 be open.

1. First, open the workbook that contains the master list.
2. Open the workbook that contains the drop down lists
3. 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)

More Tutorials

Drop Down List Basics

Hide Previously Used Items

Data Validation Tips