Excel -- Data Validation -- Use a List from Another Workbook

Create the 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.

 

Create the Source List

The following instructions assume you have a workbook named DatValWb.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 in which you wish to use the list in Data Validation.
  2. Choose Insert>Name>Define
  3. Type a name for the List, e.g. MyList
  4. 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
  5. 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.
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  
 
       Home     Excel Tips     Excel Files      Blog    Contact

 

RSS Feed

 

 

 

The Excel Store