Challenges: Final Friday Fix: November 2018 Challenge

Welcome back to the Final Friday Fix! This month, we are going to draw inspiration from the Excel team’s Ask Me Anything session earlier this month and look to Reddit to find a challenging question. (No cheating by going there to look for the answer!)

The challenge was posed: how do you create a series of dropdown boxes so that they feed off each other? For example, given data validation with a car make (manufacturer, in layman’s terms), how can that populate the next data validation to list the models associated with only that make, then the variants associated with that model and year of manufacture, and so on?

Now, this is not a difficult question – Google will generally provide an answer involving the INDIRECT function and creating a named range for each list you want to use. For example, we have three Toyota vehicles in our list, so you would set up a named range called “Toyota”, so when you enter in that value in the “make” cell, the data validation will point to =INDIRECT(“Toyota”), which gives you the list of three models that we have in our data set.

However, this results in two primary problems:

• Doing this will result in a LOT of named ranges. Taking this approach will create one named range for each possible make, plus one named range for each model within each make, and another named range for each possible year within that model – this leading on to the second issue…
• If you have repeated items such as the year of manufacture, there is no way to distinguish between a 2018 Corolla and a 2018 Fiesta. So you will need to have combination named ranges such as 2018Corolla, 2017Corolla, 2016Corolla, so you can pick out the relevant variants specific to each year. For the record, in Australia, there were 19 different variants of the Ford Focus manufactured in 2017.

This all seems a bit messy. So this is our challenge this month – can you come up with a way of creating a cascading set of data validated cells that refer back to the value selected in the previous cell, without creating a mess of named ranges in the process?

We’ve compiled a list of cars for you to use as a dataset – two different makes (Toyota and Ford), with five different models across the makes (Yaris, Corolla, Camry, Fiesta and Focus), across three different years, and no less than 161 unique variants across the combination of years and models. This is an incredibly small dataset when compared to the actual number of different manufacturers and models and variants available in the world, so it should be clear that having a solution that is scalable is going to be critical. If we doubled the number of makes, models and years, we don’t want a solution that will require us to do a heap of work to upgrade it.

Think it’s easy? Let us know if you can think of a good way to do it at contact@sumproduct.com. We’ll publish our solution on Monday!