How to create a computable drop-down list

For example, you need to select gifts for the employee's children from the lists for different ages and have a spreadsheet with:

• Name and birth date of the child
• Age of the child calculated the following formula (see How to calculate age in Excel) for the 1^st of August as the current date:

  = DATEDIF (<start date>, TODAY (), "y")

  where TODAY () returns the current date, "y" tells DATEDIF to count full years between <start date> and TODAY ():

• The next column should contain drop-down lists that show different gift options depending on the calculated age, for example:

To create the dynamic or computable drop-down list, do the following:

   1.   For every set of options for the 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.   Add a 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 nested IF formula:

  = IF ($C2<1, 0, IF ($C2<6, first, IF ($C2<10, second, IF ($C2<17, third,))))

  or the shorter formula using IFS added in Excel 2016:

  = IFS ($C2<1, 0, $C2<6, first, $C2<10, $C2<17, third).

• Make sure to check the In-Cell Dropdown option.

Now, Excel will show different lists for different ages:

Note: If you need an automatically extended drop-down list in Excel that automatically adds new items to the list of choices, check the tip How to create an automatically extended or dynamic drop-down list.

See also this tip in French: Comment créer une liste déroulante calculable.