Excel tutorials

How to Create a Dependent Drop-Down Menus

About the drop-down menus and the creation of Data Validation I have already written on these links

Here, I'll show you two ways to create two dependent drop-down menus.

The first example:
Notice in the figure below and layout of it. In the 'A2' cell is the first drop-down list. This list pulls information from the 'D2:D4 "range of cells, in fact, these data are the same data in a range of cells' E1: G1. Ranging 'E2: G8' provides information to pull another dependent drop-down list.

How to make a dependent drop-down list


1st step.

To begin with, let's define the name for a certain range of data. About how to define the name of the cell range (15a) I wrote in the tutorial on the relevant link.

 

Define cell range names

So, define the name for the next cell range.
firstList -------> D2:D4
AAA -----------> E2:E8
BBB -----------> F2:F6
CCC -----------> G2:G5

It looks like the picture below.

Name a range of cells in Excel


In Name Manager it looks like this in the picture below

Naming the cell range in Excel Name Manager

2nd step.

Setting Data Validation

In the next step, we need to set Data Validation for the drop-down menu. It looks like the picture below.
In the 'Allow' field, set: List
In the 'Source' field, set: =firstList

Creating a drop-down menu in Data Validation

3rd step:
In the third step, let's set up the dependent Drop Down List in cell B2. By the same principle as in the previous step, set the formula in Data Validation
=INDIRECT($A$2)

Creating a dependent drop-down menu using the INDIRECT formula in Data Validation

And finally we have two dependent drop-down menus as shown below

Two dependent drop-down lists in Excel using INDIRECT formulas

Dependent drop-down list in Excel

The second way (and not the only one, there are many ways) to create a dependent dropdown list is described below. This mode is partially similar to the previous one. However, notice that we have one column of data less, as well as the formula we use. But that is why we have more named cell ranges.

How to naming Cells in Excel

 

Thus, the image above shows the cell ranges. The following cell ranges were named as follows:

firstData -------> E1:G1
aaa -----------> E2
aaaData -----> E2:E8
bbb -----------> F2
bbbData -----> F2:F6
ccc ------------> G2
cccData ------> G2:G5

Named cell ranges in Name Manager look like the picture below.

How to Name Cells in Excel Name Manager

Set First Drop Down List

The first Data Validation List is set as shown below.
In the 'Allow' field, set: List
In the 'Source' field, set: =firstData

How to set up the first drop-down menu

How to set a dependent drop-down list in Excel

To set up a drop-down list in the other dependent drop-down menu, we will use the formula below.
=OFFSET(INDIRECT(A2);1;0;COUNTA(INDIRECT(A2&"Data"));1)

Set this formula in Data Validation as shown below.

Creating a dependent drop-down menu using the OFFSET function

Finally, depending on the selected item in the first drop-down list in the second drop-down menu, we will have a list of items dependent on the first drop-down list.

Please note that you must correctly set absolute and relative addresses in formulas, especially if you copy the formula.
Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.