Search Contextures Sites

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

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.
 

Excel Data Validation Tutorials

1. Data Validation
2. Data Validation -- Create Dependent Lists
3. Hide Previously Used Items in a Dropdown List
4. Display Messages to the User
5. Use a List from Another Workbook
6. Validation Criteria Examples
7. Custom Validation Criteria Examples
8. Data Validation Tips
9. Data Validation Documentation   
10 Data Validation -- Combo box     
11. Data Validation -- Combo Box - Named Ranges
12. Data Validation -- Display Input Messages in a Text Box 
13. Data Validation -- Dependent Dropdowns from a Sorted List  
14. Data Validation -- Combo Box -- Click
15. Data Validation -- Create Dependent Lists With INDEX  
   

       Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact

 

Privacy Policy

 

Contextures Inc., Copyright © 2009.
All rights reserved.