A dynamic drop-down list is a great way to force spreadsheet users, to select a specific array of inputs for a cell in a financial model. This dynamic approach combines the Defined Name function, OFFSET and COUNTA functions to provide a drop-down list, which flexes whenever there are additions or deletions to the data array of inputs.

This example is based on the following Excel spreadsheet:

Strategize Financial Modelling – Dynamic Drop-Down List example

Practical business planning and spreadsheet applications

A dynamic drop-down list can be a valuable tool in the following spreadsheet examples:

• Pricing tool of goods or services – a company’s produce offering can change over time
• Strategic plan – a company can acquire or divest business units or assets across the years
• Executive dashboard – a company’s portfolio of companies could increase, decrease or change over several periods

Step by step approach (as per the above video)

First select “Formulas” at the top of the page, then select “Name Manager”. Choose “New…” on the top left-hard corner.

❶Define Name

Decide on the name pertaining to the range of data. This list will be called “listCountries“. Select “Workbook” Note: if you only want the name range to apply to the selected worksheet, select “Worksheet” instead.

By default leave “Comments” blank, and with “Refers to:” write the following:

❷OFFSET function

Reference

It is fundamental to reference the first country’s cell and not the title of the list, because the list does not want to include the “Country” cell (B8) in the drop-down list.

Rows

This has be to zero because the defined name must reference the first cell in B8.

Cols

The same – zero. For similar reason because first cell in B8 must be referenced.

=OFFSET(‘Dynamic_Drop-Down_List_Example’!$B$9,0,0,

Height

Given the list runs vertically, we must insert the following formula into “[height]”:

❸COUNTA function

Remember to use COUNTA and not COUNT. COUNT can only count an array of numbers (integers), whilst COUNTA is able to count both text and integers.

It must reference the entire range of cells, which you want to include, as well as blank cells that you might potentially use in the future – when you want to add to the current list. Remember to reference the cell above the first country, “Argentina”; which in this case is cell B8.

The final element of the formula, “[width]”, please leave blank. Don’t forget to type “-1” at the end of the COUNTA function – the defined name will not reference properly.

COUNTA(‘Dynamic_Drop-Down_List_Example’!$F$8:$F$80)-1)

❹ Data Validation

Trying out the Drop-down Box! As per the following, selecting a cell(s), choose “Data” and then “Data Validation,” and choose “List” under “Settings”.

Then enter the name of the list, “listCountries” in “Source”.

listCountries

Testing the Dynamic drop-down list

Now test the drop-down box out, by adding some additional names in cells B20 and B21 and see how the drop-down box seamlessly and dynamically updates to reflect these new additions!