Copy drop down list in Excel

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.

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 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.xls workbook.

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.xls
• There is a list of customers, in a range named CustName

If you are using your own file with a master list, substitute that file's name and list name in the instructions below. For instructions on creating a named range, refer to Naming Ranges.

Create a Reference to Master List

Next, open the workbook in which you will create the drop down lists. Then, follow the steps below, to create a new name in that workbook

1. On the menu bar, choose Insert>Name>Define
2. Type a name for the List, e.g. MyList
3. In the Refers To box, type an equal sign, then the workbook name, an exclamation mark, and the range name. Do NOT use square brackets around the workbook name. For example:
   =DataValWb.xls!CustName
4. Click OK

NOTE: If the workbook name has spaces, type an apostrophe at the start and end of the name, e.g.:
='Data Val Wb.xls'!CustName

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. Choose Data>Validation
3. In the Allow box, choose List
4. In the Source box, type the list name, preceded by an equal sign, e.g.: =MyList
5. Click OK

Use the Drop Down List

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

1. 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.

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.

Get the Sample Files

Get the zipped Drop Downs from Other Workbook sample files. There two files and both are in xls 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

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.