This approach uses the LOOKUP to produce a dynamic dependent drop-down list, which will cater to dependent lists of various input quantities. The application of a dynamic dependent drop-down list, can be applied in a variety of financial models and spreadsheet situations.

Strategize Financial Modelling – Dynamic dependent Drop-Down List example – Advanced with LOOKUP.xls

Problems with a standard dependent drop-down list – using INDIRECT

A standard dependent drop-down list employs the INDIRECT function. This is fine if all the dependent lists contain fixed ranges of variables – that is they won’t change.

But what if our Asia region is expanding? In other words, we are opening up in South Korea, Thailand and Vietnam. For the purposes of this example, we will disregard the obvious need of populating the below Sales Volumes by Business Unit.

Clearly we need a workaround solution, because the addition of these 3 new Business Units renders the existing dependent-drop down list redundant. Our list must be dynamic. The use of the INDIRECT function approach is no longer viable.

Obviously we need a solution, which caters to various dependent lists of altering components.

Using a LOOKUP table to achieve a dynamic dependent drop-down list

One solution is the creation of a table of values, based on the precedent value selected in Regions list in cell B7. One column must be copied across, such as range H8 to H17 – across to column J. The title value is sourced from cell B7.

Next the LOOKUP function must be applied, in order to reference the correct, corresponding value from the 5 region lists. One alternative is to use the HLOOKUP – but this exercise will stick with the LOOKUP function.

It is vital to apply absolute cell referencing to the lookup_vector, whilst applying relative referencing for the result_vector – because we want to reference the corresponding row down the table.

We can see the LOOKUP is returning zeros – if there is not a corresponding business unit. We will format the range to 2 quotation marks – but remember we will need to factor out the zeroes in the next part.

Creating a Defined Name for the dependent LOOKUP table

The final element will reference this LOOKUP table, which we just created. The defined name range, listBusUnits, will replace the INDIRECT function – which cannot cater for dynamically changing or varying dependent lists of business unit names.

Go to the Name Manager. Create a New Name. It is called “listBusUnits“.

Take your time and if needs be – feel free pause this video. This referenced formula for the table in column J must be 100% accurate – otherwise we won’t achieve the desired result.

Be aware for the OFFSET part, reference the first cell in the range of Business Unit countries – not the title cell. Remember the COUNTA rather than the COUNT function is used, COUNTA will returns the count of cells that contain any type of cell value (i.e. which are not blank). We must use COUNTIF to disregard those cells containing zeroes – which we formatted to 2 quotation marks before.

Changing the source formula in Data Validation

Go to cell D7. Now we must go to Data Validation, and change Source formula to listBusUnits. Let’s check our dependent drop-down list. Great it’s working.

We will add some new, hypothetical business units across the various regions. Perhaps we want to divest or close a business unit – as we will do for the “MidEast“. We now change the source Region, and immediately see the LOOKUP table change too.

We can clearly see the LOOKUP table and dependent drop-down list are changing and flexing correctly.

Conclusion

The use of a LOOKUP table and defined name range referencing, can create a dynamic dependent drop-down list for a financial model. This approach will be able to dependent lists, which contain differing ranges of variables – even if these ranges change in scope.