Suppose we have items of different criteria like Fruits, Vegetables, Flowers, Animals, and Birds. Each criterion has a list of items as above:
1.
2.
3.
Here is a process to create a drop down:
Drop Down No: 1
- First select a cell to add the drop down, for the list of criteria.
- Select ‘Data’ from the ribbon.
- Select ‘Data validation’ from the menu.
4. Click on the ‘Data validation’ In the validation criteria, select ‘list’ in ‘allow’ box and type in the following formulae in ‘source’:
=$C$2:$G$2
The above formula indicates the range of cells in which the list of criteria is available.
5. When we click on the drop down, we will be able to see the list of criteria.
Drop Down No: 2
Now suppose we want to retrieve ‘Apple’ from criterion ‘Fruits’.
- Select the items of ‘Fruits’ and click in the ‘Name box’ which is at the top, left hand side of the sheet.
- Type the name of the criterion, ‘Fruits’ which we had selected. Then press enter key.
3. Select a cell to add the drop down, for the list of items.
4. Select ‘Data’ from the ribbon.
5. Select ‘Data validation’ from the menu.
6. In the validation criteria, select ‘list’ in ‘allow’ and type in the following formulae in ‘source’:
=INDIRECT ($D$9)
What this does is this:
=INDIRECT (ref-text, [a1])
Ref_text is a reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference, or a reference to a cell as a text string. If ref_text is not a valid cell reference, INDIRECT returns the #REF! Error value.
A1 is a logical value that specifies what type of reference is contained in the cell ref_text.
Here, we are using the formula in drop down. So we need to give the reference cell number (D9).
Now the drop down for list of items is added. Now the required item
can be selected from the list.
In the similar way we can do for all the criteria, and select the required item. In this way we can create dynamic drop downs in excel.