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:
Strategize 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”
❹ 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.