A Drop-down list that depends on the selection made using another Drop-down list is called a Conditional or Dependent drop-down list. Following is an example of the same.
As you can see, when we go for the degree B.sc in column C, the options are Physics, Mathematics & Chemistry. For B.A, the options are Malayalam, English & Hindi and for B.Tech, the options are Civil, Mechanical, and Computer.
Let’s see how to create Dependent drop down lists like these.
Select the cells where you want the first drop-down list. Go to the Data tab > Data Tools > Data Validation
In the dialog called Data Validation > Settings > Select ‘List’ from the options under the heading Validation criteria.
Now we have to specify the source for the drop-down list. In this case, different degrees such as B.Sc, B.A and B.Tech F3 will be the options and the cells F3 to H3 have this data.
In the Input box under the heading ‘Source’, either type in the address of the cells containing the source data (F3:H3) or select those cells using the mouse.
$ is added before the Row and Column indexes to make the references Absolute.
Click OK and you will have a drop-down list in the selected cell/cells.
This drop-down list containing degrees is the Main/First drop-down list. To create the Second drop-down menu that depends on the selection made in the first one,
Select the entire dataset > Go to the Formulas tab > Click on Create from selection
In the Create Names from Selection dialog, mark the checkbox for Top row and Click OK.
This action will create 3 Named Ranges ‘B.A’, ‘B.Sc’, ‘B.Tech’ and will refer to the cells F4:F6, G4:G6, and H4:H6 respectively.
Select the cells where you want the second drop-down list. Go to the Data tab > Data Tools > Data Validation.
In the dialog called Data Validation > Settings > Select ‘List’ from the options under the heading Validation criteria
In the Input box under the heading ‘Source’, type in the following formula
=INDIRECT(C3)
Let me explain how this formula works.
When the cell C3 has the value ‘B.A’, the INDIRECT function will convert this text into a valid reference that refers to G4:G6. Similarly, B.Tech will be converted to a reference that refers to H4:H6.
Click OK.
Now when you make a selection in the first drop-down list, the second drop-drop list will show the corresponding options.
