Data Validation List from Another Workbook

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.


IMPORTANT: For data validation 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 the Excel Data Validation Source List

The following instructions assume you have a workbook named DataValWb.xls, which contains a range named CustName.

For instructions on creating a named range, refer to Naming Ranges .

Create a Reference to the Source List

  1. Open the workbook that contains the source list -- DataValWb.xls in this example.
  2. Open the workbook in which you wish to use the list in Data Validation.
  3. Choose Insert>Name>Define
  4. Type a name for the List, e.g. MyList
  5. In the refers to box, type a reference to the named range. Start with an equal sign, then the workbook name and an exclamation mark, followed by the range name, e.g. =DataValWb.xls!CustName
  6. Click OK

Create the Dropdown List

  1. Select the cells in which data validation will be set.
  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

Watch the Data Validation Video

To see the steps for creating the data validation drop down from a list in another workbook, you can watch this short Excel video tutorial.

Create a Dynamic Range from Another Workbook

You can create a dynamic range that refers to a dynamic range in another (open) workbook.

  1. Create and save a workbook (MyLists.xls, in this example)
  2. Enter a list of names in cells A1:A10 on Sheet 1.
  3. To create a dynamic range, choose Insert|Name|Define
    Use Employees as the range name, and the following formula:
  4. Keep MyLists.xls open, and create and save a new workbook (Schedule.xls)
  5. In Schedule.xls, create a range named EmployeeList with this formula:
  6. In cell A1 of sheet1, enter the following formula:
  7. Copy the formula down to row 200 (or any row beyond the length of the dynamic range in MyList.xls). Note: many of the rows will contain a #VALUE! error.
  8. In Schedule.xls, create another range, with the name NoErrors, and the formula:

     =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A$1:$A$300,"#VALUE! "))
     (all one line)

  1. Use NoErrors as the source for your Data Validation list.

Use the Data Validation List

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

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

Get All the Excel News

For regular Excel news, tips and videos, please sign up for the Contextures Excel newsletter. Your email address will never be shared with anyone else.


Search Contextures Sites



Excel Tutorials - Data Validation

Data Validation Basics

Hide Previously Used Items

Data Validation Tips

Search Contextures Sites


Excel Data Entry Popup List


Excel Data Entry Popup List



Last updated: March 22, 2016 7:44 PM