Today I am going to show you one approach using the LOOKUP, which will produce a dynamic dependent drop-down list.
This can be applied in a variety of financial models and spreadsheet solutions.
This illustration contains a drop-down list by Region sourced from the range B9 to B17.
The Total Revenue and COGS calculations in G6 and H6 respectively,
are sourced from the corresponding Unit Revenue and Unit Cost columns by region.
Whilst the Sales Volume driver is sourced from the Sales Volumes by Business Unit – at the bottom.
Therefore, the dependent drop-down list in cell D7 must reflect the precedent cell value in B7 – Region.
Here in lies the problem. 0:01:20.033,0:01:20.000
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.
Firstly. Let’s look at the Region list.
It is sourced from a Defined Name range, which flexes based on the number of inputs in the range.
The dependent drop-down list applies the INDIRECT function,
because it assumes the dependent ranges are fixed and uniform; across the five tables of countries.
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.
We now need to correct our existing problem with the dependent drop-down list in cell D7.
The use of the INDIRECT function approach is no longer viable.
Clearly we need a solution, which caters to various dependent lists of altering components.
One solution is the creation of a table of values, based on the precedent value selected in Regions list in cell B7.
We will copy one of the columns, such as range H8 to H17 – across to column J.
The title value is sourced from cell B7.
Next we will use the LOOKUP function, in order to reference the correct, corresponding value from the 5 region lists.
One alternative is to use the HLOOKUP – but we 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 – 0:04:20.033,0:04:20.000
because we want to reference the corresponding row down the table.
The list of Asia business units is correctly referencing.
The same for Americas.
We can see the LOOKUP is returning zeros – if there is not a corresponding business unit.
We will format the range to two quotation marks –
but remember we will need to factor out the zeroes in the next part.
The final element will reference this LOOKUP table, which we just created. 0:05:20.066,0:05:20.033
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 Name
I’ve called it listBusUnits
Please take your time, and if needs be; pause this video.
This referenced formula for the table in column J must be 100% correct –
otherwise we won’t achieve the desired result.
Remember in 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 return 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.
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 now 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 will now change the source Region, and immediately see the LOOKUP table change too.
As explained earlier, we would need to add the Sales Volumes for these additional Business Unit countries below –
but for the purposes of time, we will overlook this for this video.
We can clearly see the LOOKUP table, and dependent drop-down list
are changing and flexing correctly.
In conclusion, the use of a LOOKUP table and defined name range referencing can create
a dynamic dependent drop-down list for a financial model.