Constructing dependent drop-down lists in your financial spreadsheet

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

Here is a quick way to add a level of sophistication and reduce input error in your spreadsheet. It is a great tool because it will flex, based on the value selected in a precedent drop-down list.

(Click on image to enlarge)

In the above screenshot, select Canada for cell H63. The dependent drop-down list will be created in cell I63. Once it is completed, it will only give spreadsheet users the choice of Canadian Provinces or US States.

For this to work, it is imperative to define the data arrays for both the Provinces and the States; with the exact same name as listed in the drop-down list in the above screenshot.

Select Formulas, choose Name Manager, and click on New… and complete the following detail – Name: Canada, Scope: Workbook and Refers to: [place relevant range of the Canadian Provinces].

Repeat the above instructions for the array of US States.

To complete this exercise, select Data in the menu at the top of the spreadsheet and select Data Validation.

Under the Settings tab, click on the drop-down list under Allow and select List. In the Source window type the following formula: =INDIRECT(H63). As demonstrated earlier, it is imperative the array of both Canadian Provinces and US States are defined (by name) identically to their names in the Country column, otherwise the dependent drop-down list will return an error.

(Click on image to enlarge)

This video will provide you with further guidance for creating dependent drop-down lists.