How to create a computable drop-down list
For example, we need to select gifts for the employee’s children from the lists for different ages. So, there is a spreadsheet with:
- Name and birthday date of a child
- Calculated children’s age using the formula (see
How to calculate an age in Excel)
for the 1st of August as current date:
= DATEDIF ( <start date>, TODAY (), “y”)
where TODAY () calculates current date, “y” calculates complete years between <start date> and TODAY ():
- The next field should contain drop-down list dependent on calculated age, for example:
To create the computable drop-down list, do the following:
1. For every dependent drop-down list, create a named range (see Creating and using named ranges), for example:
- First – for children from 1 to 5 years
- Second – for children from 5 to 9 years
- Third – for children under 9 years:
2. Create the drop-down list (see Creating a Drop-Down List in a Cell):
2.1. On the Data tab, in the Data Tools group, click Data Validation:
2.2. In the Data Validation dialog box, on the Settings tab:
- In the Allow drop-down list, select List.
- In the Source box, specify the range, using the formula:
= IF ( $C2<1, 0, IF ( $C2<6, first, IF ( $C2<10, second, IF ( $C2<17, third,)))).
- Make sure that the In-Cell Dropdown option is checked.
Excel will show different lists for different ages: