Excel Drop Down List From Different Workbook

In 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 — as long as the other workbook is open.

The instructions are below, and watch the video to see the steps. The demo uses Excel 2010, and it’s the same in Excel 2007, except that there is an Office button, instead of a File tab.

Drop Down from a List in Another Workbook

Important Note: For the data validation to work, the workbook that contains the list must be open, in the same instance of Excel. Users will have to open both the workbooks — the one with the drop down lists, and the workbook with the original source list.

The following instructions are for Excel 2007 and later. On my website, there are similar instructions to create a data validation list from a different workbook in Excel 2003.

Create the Source List

In this example, you have a workbook named DataValWb.xlsx, which contains a list of customer names. This list is in a range named CustName. (For instructions on creating a named range, refer to Naming Ranges.)

Create a Named Reference to the Source List

Next, you’ll create named range in the workbook where the drop down list will be added.

1. Open the workbook that contains the source list — DataValWb.xlsx in this example.
2. Open the workbook in which you wish to use the list in Data Validation, or create a new workbook.
3. On the Ribbon, click the Formulas tab, then click Define Name.
   • 
4. Type a name for the List, e.g. MyCustList
5. From the Scope drop down, select Workbook
6. Click in the Refers to box — this is where you’ll type a reference to the named range in the source workbook
7. Type an equal sign, then the source workbook name and extension. Do NOT include any square brackets in the name. For example:
   =DataValWb.xlsx
8. OR, if the workbook name includes spaces, start and end the name with an apostrophe. For example: ‘DataVal May10.xlsx’
9. Next, type an exclamation mark — !
10. Finally, type the range name in the source workbook
11. The completed reference should look like this:
    =DataValWb.xlsx!CustName
    OR =’DataVal May10.xlsx’!CustName
    
12. Click OK

Create the Dropdown List

1. Select the cells in which data validation will be set.
2. On the Ribbon, click the Data tab, then click Data Validation
3. In the Allow box, choose List
4. In the Source box, type an equal sign, then the list name that you just created, e.g.: =MyCustList
5. Click OK

Use the Data Validation List

Remember, to use the data validation drop down list, both workbooks must be open.

1. Open both workbooks — the one with the drop down lists, and the workbook with the original source list.
2. Select an item from the data validation drop down list.

Watch the Video

Watch this video to see the steps for creating a drop down from a list in another workbook. You’ll see how to set up the named ranges, and the drop down lists. The demo is in Excel 2010.

________________________________