As a result of prodding around the excel subreddit I found a query from user innocuous_gorilla about creating drop downs that are dependent upon each other. Due to working at NHS England where we have a massive array of organisation types and hierarchies, I quickly learnt how to do this! For example, in NHS England we have different regions, sub-regions and commissioning groups which tend to be massive long lists if un-filtered, so it makes it hard to select one in a drop down where all options available. Using the following method, you can make a set of drop downs that all depend on a master drop down(s), so choosing a region or sub-region filters the commissioning group drop down box to only those groups that belong to the chosen parent region.
Here’s the link to the workbook example I’ll be walking through in the following tutorial, where we’ll deal with the example brought up by innocuous_gorilla.
We’ll tackle the workbook a section at a time, where I’ll describe what each part is for and how it works.
Part 1
This is our named range index. We’ve included this to make it a bit easier to track what named ranges are available within the spreadsheet, and what each named range is responsible for. I’ll be referencing these later as these are used with our combo boxes.
Part 2
Here we’ve simply typed a list for each of our options. The product list will be used to drive our first drop down (all drop downs used in this example are Combo Boxes), so we’ve saved this as a named range (follow this example in my previous tutorial to understand how to make a named range if you are unaware).
Part 3
Next up to the right of these lists, we’ve got a couple more lists which separate out the potential options that each of our products has available, note these are all differing lengths which makes it a bit harder to deal with when creating our dynamic lists.
Part 4
Next, we have a table giving us the maximum number of each option available to each of our products. This will allow our colour and size lists to populate dynamically and be the correct length. This can be generated by counting the tables in part 3 but were typed this time as it was easy to do so.
Part 5
Now we have our combo boxes – these have been populated using our named ranges. Each of the ‘List’ named ranges informs the corresponding combo box, and the ‘LinkCell’ named ranges are the Cell linkage for each corresponding combo box (if you’re lost check my other tutorial that covers how combo boxes work). The grey cells to the right of the drop downs are our ‘LinkCell’ and ‘Selected- -‘ named ranges. The ‘Selected’ named ranges have the following formulae:
=IFERROR(INDEX(ColourList,ColourLinkCell),INDEX(ColourList,$E$20))
This is indexing our ‘List’ named range and selecting the colour that corresponds with the linked cell. If this ever causes an error (which will happen when a lower-level drop down (in this case our colour and size drop downs) has a linked cell that is out of the range of the index, which occurs when changing between products if the product’s max number of colours/sizes is less than the previous selected product), we simply select the max possible option within the list (E20 is our max number of colours of the selected product).
Part 6
Finally we have our Colour and Size lists. The yellow cell to the left (both follow the same structure) has the following formula:
=INDEX($B$14:$B$16,ProductLinkCell)
Here we’re simply checking the table of maxes for the max number of the colours of the selected product, by indexing the column of colours based on the chosen product row number.
The counting numbers on the left of each list have the following formula:
=IFERROR(IF(E21+1>$E$20,"",E21+1),"")
This counts to the max number of the currently selected product for colours/sizes. We dragged this down to the potential largest max number for each option (4 for colours and 3 for sizes). If there’s an error or the number is greater than the max, we instead show a blank (“”).
The lists themselves are made up of the following formula:
=IFERROR(INDEX($H$3:$J$6,$E21,MATCH(SelectedProduct,$H$2:$J$2,0)),"")
This indexes the tables in part 3, looking for the row number corresponding to the cell to the left of the current cell and looking for the column corresponding to the currently selected product. If an error occurs, we show a blank (“”).
Part 7
These lists are then turned into the ‘List’ named ranges which use the following formula:
=Sheet1!$J$21:INDEX(Sheet1!$J$21:$J$23,Sheet1!$I$20)
This begins an array at the start of the list, then indexes the entire list to end the array at the maximum possible number for the selected product.
Hopefully understanding how each element works will help you to break this down and apply it to your own work! Thanks so much for reading and I hope this helped. If you have any further queries please ask in the comment section below.
Static Interest 