Creating dynamic and seamless drop-down lists in your financial model

Simon Selkrig, Strategize Financial Modelling, simon@strategizefm.com, Montreal, QC

Here is a quick and simple way to incorporate a dynamic approach to a drop-down list of inputs. It is more bulletproof than creating a dynamic name range.

First select “Formulas” at the top of the page, then select “Name Manager.”

Choose “New…” on the top left-hard corner.

Decide on the name pertaining to the range of data. This list will be called “listBusUnits.”

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(Tableau_BO!$B$12,0,0,COUNTA(Tableau_BO!$B$11:$B$40)-1,)

The first element of the OFFSET formula, the “reference,” must reference the first value in the list – in this example it is “Alberta Mining Ltd” in cell B12.

Remember in the next two parts, “rows” and “cols” to leave them blank or enter zero values (as per the above). Given the list runs vertically, we must insert the following formula into “[height]”:

COUNTA(Tableau_BO!$B$11:$B$40)-1

It must reference the entire range of cells, which you want to reference, 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 Business Unit, “Alberta Mining Ltd”; which in this case is cell B11.

The final element of the formula, “[width]”, please leave blank.

Remember

Keep the selected data array referenced in the formula free of other information. The power of this approach to creating dynamic drop-down boxes is the ability to seamlessly update (add or remove) the inputs in the data range.

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, “listBusUnits” in “Source.”

Final Word

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