Set Up Dependent Dropdowns In MS Excel Using OFFSET And SUMPRODUCT

#Excel #Dropdowns #DependentDropdowns #OFFSET #SUMPRODUCT

In this article we will demonstrate an easy way to set up a dropdown that is dependent on another dropdown. For example, we select a country in cell F1 and this changes the list of cities available for selection in cell F2, as shown in the picture.

Let us assume that we have already set up the dropdown for the country with the reference to range A1:C1, then we can set up the list of cities using the formula below, where:

• OFFSET returns the range for the dependent dropdown
• A2 sets the starting cell for OFFSET
• 0 tells OFFSET not to shift vertically
• MATCH(F1;A1:C1;0)-1 tells OFFSET how many columns it should shift to the right from the starting cell A2
• SUMPRODUCT((F1=A1:C1)*(A2:C3<>"")) tells OFFSET the number of the non-empty cells (A2:C3<>"") in the selected column (F1=A1:C1)

=OFFSET(A2;0;MATCH(F1;A1:C1;0)-1;SUMPRODUCT((F1=A1:C1)*(A2:C3<>"")))        

The picture bellow demonstrates the dependent dropdown of cities when Ukraine is selected in the cell for the country, where:

• (F1=A1:C1) is array {FALSE;TRUE;FALSE}
• (A2:C3<>"") is array {FALSE;TRUE;TRUE:FALSE;FALSE;TRUE}
• (F1=A1:C1)*(A2:C3<>"") is array {0;1;0:0;0;0} because the product of FALSE*FALSE or FALSE*TRUE is equal to 0, whereas the product of TRUE*TRUE is equal to 1
• SUMPRODUCT returns the sum of array {0;1;0:0;0;0}, which is equal to 1 in our case

The demonstrated approach of setting up the dependent drop-down lists is the most plain and simple of all possible.