![]()
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.
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 .
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A)-COUNTIF(Sheet1!$A$1:$A$300,"#VALUE! "))
(all one line)
- Use NoErrors as the source for your Data Validation list.