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

Thank you for visiting OfficeToolTips

We are glad to help you in your work. However, you are using ad blocker and our efforts will not be rewarded.

Please consider disabling ad blocker before continuing the reading.