Leading the way in Microsoft Office Development

Displaying a Dependent Drop-Down List

In this example we are going to create two drop-down lists.

Depending on the value selected in the first drop-down list will determine what values are displayed in the second.

Create a table with all the corresponding values

In this example we are going to have 3 categories (Excel, Word and PowerPoint) each one has 6 different values.

Create the Named Ranges

Highlight the cells "E3:E5".

Select (Insert > Name > Define) and type the name "Category".

Highlight the cells "F3:F8". Select (Insert > Name > Define) and type the name "Excel".

Highlight the cells "G3:G8". Select (Insert > Name > Define) and type the name "Word".

Highlight the cells "H3:H8". Select (Insert > Name > Define) and type the name "PowerPoint".

Enter the Criteria for the first Drop-Down List

Select the cell you want the first drop-down list to appear in, in this case cell "C2".

Select (Data > Validation) and select the Settings tab.

In the "Allow" drop-down box select "List".

In the "Source" box enter the formula "=Category" to refer to the named range that you created earlier.

Enter the Criteria for the second Drop-Down List

Select the cell you want the first drop-down list to appear in, in this case cell "C3".

Select (Data > Validation) and select the Settings tab.

In the "Allow" drop-down box select "List".

In the "Source" box enter the formula "=INDIRECT($C$2)" to refer to the named range that you created earlier.

Displaying the PowerPoint Values

You now have two dependent drop-down list boxes where the values in the second list depend on the selection made in the first drop-down.