How to create a computable drop-down list

Excel 2016
When you fill data in some spreadsheet, it can be necessary to show different drop-down lists that depend on some filled or calculated values.

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():

Example of age calculation in Excel 2016
  • The next field should contain drop-down list dependent on calculated age, for example:
Example of computed drop-down list in Excel 2016

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:
Example of named ranges in Excel 2016

   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:

Data Tools group in Excel 2016

   2.2.   In the Data Validation dialog box, on the Settings tab:

Data Validation in Excel 2016
  • 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:

Example of a computable drop-list in Excel 2016

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.