This article demonstrates how to set up dependent drop-down lists in multiple cells. The drop-down lists are populated based on the adjacent value on the same row, in other words, they are dependent on each other. Select Region "East" demonstrated above and you can only select Products "BB" and "EE", the table in cell range B2:C11 determines how they are related.
Regular drop-down lists used here are easy to insert and customize, the formulas in this workbook are easy to create and so are the named ranges as well. I will in detail explain how they work.
The downside with this approach is that you can only use two connected drop-down lists. For example, three dependent drop-down lists actually require a three-dimensional table which is possible using VBA, however, outside the scope of this article. Note, to be clear, there is no VBA code in the example demonstrated here.
Another downside with regular drop-down lists is that if you select a Region value and then a Product value and then go back and change the Region value to another value the Product value stays the same. It would have been better if that value became blank in those occasions.
It is also possible to paste a value and overwrite the drop-down list, this is how they are designed and nothing I can do about more than using perhaps VBA code or Event code to prevent such scenarios.
Cell range B2:C11 contains an Excel defined Table that automatically expands or shrinks when values are added or deleted respectively, you don't need to adjust cell references each time you edit the Excel defined Table which is a huge time saver.
There is also another data set, shown below, that is built using two array formulas. The data set extracts items (Products) for each possible value (Region) which makes it easier to populate the drop-down lists using only formulas and named ranges.
This table is dynamic and changes instantly whenever the source table is edited. Don't worry, I will explain the table above and the formulas used later in this article.
Click the checkbox "My table has headers" if your data set has headers.
Click OK button.
The main reason I am using an Excel defined Table here is that you can use a "structured reference" which is basically a table name instead of a regular cell reference.
Relationships between categories
The first array formula returns unique distinct values in cell H2:M2 from named range Region. The second array formula in cell range H3:M7 returns coherent values from each region. All of these calculations can be hidden or placed on a calculation sheet.