In Excel 2003, limit choices in a data validation drop down list, based on selection in another cell. Example based on sorted list of regions and customers
For newer versions of Excel, see the instructions here: Dependent Dropdowns from a Sorted List
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.
Two 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):
On 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.
There are detailed instructions for creating data validation lists here: Data Validation -- Introduction
The next step is to create the dependent data validation dropdown lists in column C.
=OFFSET(RegionStart, MATCH(B2,RegionColumn,0) -1,1, COUNTIF(RegionColumn,B2),1)
The OFFSET function has the following arguments:
We want the OFFSET function to return a reference to the range
of cells that contains the Ontario customers.
Reference: In our formula, the reference is RegionStart, cell A1 on the ValidationLists sheet.
Rows: How many rows down from the reference cell should our range start? The MATCH function finds the first instance of Ontario in the RegionColumn, in row 6. We subtract 1 from this number, because the starting cell is in row 1.
Columns: We want a range that is 1 column to the right of the RegionStart reference.
Height: The COUNTIF function counts the number of times that region is entered in the RegionColumn. There are 9 customers in the Ontario region.
Width: We want a range that is 1 column wide
Download the zipped sample file
Last updated: April 2, 2023 11:14 AM