How to create a dynamic drop-down list from a horizontal range

This is a follow-up to “How to create a dynamic drop-down list in your financial model”, but this article focuses on creating a dynamic drop-down list from a horizontal range or data array. A dynamic drop-down list can equally update and expand seamlessly, whenever additional items are added to a horizontal array of data.

 

Financial modelling benefits of a dynamic drop-down list

The practical benefits of adopting a drop-down list are clear in financial modelling. A debt tranche schedule in a strategic plan (financial planning & analysis), which includes a drop-down list sourced from the relevant time series to specify commencement or retirement date of a debt tranche.

In what-of analysis, a dynamic drop-down list sourced from an executive dashboard would prevent erroneous non-periodic selections; which are not within the time-frame of a strategic plan or forecast.

The following Excel spreadsheet demonstrates how to a dynamic drop-down list, when it is sourced from a horizontal range:

drop-down listStrategize Financial Modelling – Drop-Down List example – Horizontal Range.xls

 

Components of a drop-down list from a horizontal range

A defined name must be given to the drop-down list. In this example, “listMetric” was used as the defined name.

 

Name Manager

In the Name Manager, select “New” and on the “New Name” page type “listMetric” in “Name:”. Keep the “Scope:” to “Workbook” by default. The “Comment:” section is optional, thus it can be left blank. In the “Refers to:” type the following:

 

❷ OFFSET function

Decide on the first cell to be referenced in the drop-down list. In this case it is “’Drop-Down_List_Horizontal_Range’!$D$8”. The “rows” and “columns” parameters are zero, and keep the “[height]” parameter blank:

=OFFSET(‘Drop-Down_List_Horizontal_Range’!$D$8,0,0,,

 

❸ COUNTA function

The breadth of the range sourced in COUNTA is subjective; if you anticipate additions to the data array in the future, then factor this into your formula:

COUNTA(‘Drop-Down_List_Horizontal_Range’!$D$8:$P$8))

The formula is complete. Click “OK” and close out of “Name Manager”

Dynamic drop-down list

❹ Data Validation

Go into “Data Validation”, on the “Settings” tab on the “Validation Criteria” under “Allow:”, select “List” from the drop-down list. Under “Source:”, type the following:

=listMetric

 

Important business planning implications of a dynamic drop-down list

Remember to avoid inadvertent or erroneous data entry in the source data array, which are not relevant or correct for the purposes of the drop-down list. If any of the columns or cell in the source range are deleted or formatted (i.e. inserted), then the drop-down list might be compromised and impacted.