Search Contextures Sites

 

 

Contextures
Excel news
by email

 

30 Excel Functions in 30 Days

 

 

Learn how to create Excel dashboards.

 

 

Your worksheet formulas can create traffic-light charts, highlight chart elements, assign number formats, and more.

 

 

 

 

 

Data Validation List from Another Workbook

Create the Excel Data Validation Source List
Create a Reference to the Source List
Create the Dropdown List
Create a Dynamic Range from Another Workbook

You can use a list from another workbook as the source for a Data Validation dropdown list.

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.

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.

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:
         =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A))
  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:
         =MyLists.xls!Employees
  6. In cell A1 of sheet1, enter the following formula:
         =EmployeeList
  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

Search Contextures Sites

 

 

Excel Tutorials - Data Validation

Data Validation Basics
Create Dependent Lists
Dependent Dropdowns from a Sorted List  
Dependent Lists With INDEX  
Hide Previously Used Items in a Dropdown List
Display Messages to the User
Display Input Messages in a Text Box 
Use a List from Another Workbook
Criteria Examples
Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box     
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items  

 

 

 

Privacy Policy

 

Contextures Inc., Copyright 2014
All rights reserved.