You can limit the choices in an Excel Data Validation list, by using named ranges and the INDIRECT function, as explained here: Data Validation — Create Dependent Lists
Another method is to use the OFFSET function, to extract items from a sorted list, as described below. In this example, a region is selected in one column, and the customers in that region will appear in the data validation list in the adjacent cell. |
|
Set up the WorkbookTwo worksheets are required in this workbook.
On the ValidationLists sheet, type the lists that will be used in the data validation dropdowns:
Name the following ranges (there are Naming instructions here: Name a Range): |
|
Create a Data Validation Dropdown ListOn the ValidationSample sheet, type the headings Region and Customer, in cells B1 and C1. The next step is to create the Region data validation dropdown lists in column B.
|
|
|
|
|
Test the ValidationDownload the zipped sample file
|