Home > Validation > Drop Downs > Other Workbook

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 drop-downs, 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 Reference to Master List

Next, follow the steps below, to create a named range in the spreadsheet 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 Excel drop down lists, based on the name that you just created.

1. First, select the cells where you want the drop down lists.
2. Next, on the Excel Ribbon's Data tab, in the Data Tools group, click Data Validation
3. Then, in the Data Validation dialog box, go to the Settings tab
4. Click in the Allow box, and in the drop-down menu, select List
5. Nexr, click in the Source box, then press the F3 key, for a shortcut to open the Insert Name box
6. In the list of names, click on the source list name, MyCustList, to insert it
7. Next, click OK, to close the Paste Name dialog box
8. The selected name appears in the Source box, with an equal sign in front of it
9. (optional) Go to the Error Alert tab, and select settings to show error alert for invalid data and error message warning.
10. (optional) Go to the Input Message tab, and create a pop-up message that shows when the drop down cell is selected settings
11. Click the OK button, to close the Data Validation window, and to create the Excel drop-down list in the selected cell range.

Use the Drop Down List

To use the data validation drop down lists, both workbooks must be open.

Note: In the screen shot below, the customer order information is stored in a named Excel table. If you add a new customer order at the end of the table, the table will expand zutomatically, to include the new row. Also, an in-cell dropdown list should be added automatically, in any new rows.

1. First, open the workbook that contains the master list.
2. Next, open the workbook that contains the drop down lists
3. On the worksheet, select a cell in the Customer column, where the drop downs are
4. Next, click the drop-down arrow that appears at the right side of the cell
5. In the data validation drop down list, click on one of the customer names, to select it.
6. The drop down list closes, and the selected name is inserted in the cell

In the screen shot below, there are drop downs in column A, where you can select a customer name. The master list of customer names is stored in a different workbook

Get the Practice Files

Download 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 the master file first (DataValWb.xls), and then open the other workbook.

More Tutorials

Drop Down List Basics

Hide Previously Used Items

Data Validation Tips