I was able to use Data Validation to make a dependent drop-down list. When a particular choice is selected in Column A, it filters the choices in Column B. I accomplished this using the INDIRECT function as the Data Validation setting in Column B. Unfortunately, I could only get this to work by using Tables in order to name the range Column B is filtered by. The problem is I want a third-level, which could be accomplished in the same manner, BUT that requires making lots of new tables. There are 10 choices available in Column A, each of those has between 2 and 10 choices to appear in Column B. Let's call it an average of 5... that means the potential of having 50 tables in order to get my sub-sub-category.
Is there a better way to do this? Is there a way to make a single large table, rather than many individual tables. For example, see the sample below.
| Category | Sub Category | SubSub Category |
|---|---|---|
| Cat 1 | Sub 1-1 | SubSub 1-1-1 |
| Cat 1 | Sub 1-1 | SubSub 1-1-2 |
| Cat 1 | Sub 1-2 | SubSub 1-2-1 |
| Cat 1 | Sub 1-2 | SubSub 1-2-2 |
| Cat 2 | Sub 2-1 | SubSub 2-1-1 |
| Cat 2 | Sub 2-1 | SubSub 2-1-2 |
| Cat 2 | Sub 2-2 | SubSub 2-2-1 |
| Cat 2 | Sub 2-2 | SubSub 2-2-2 |
Maybe there is a way to use VLOOKUP?, but I would need it to return multiple results with no duplicates.
Does anyone have suggestions?
I can use the INDIRECT function, its just unwieldy to have so many individual tables. I tried using an OFFSET function, but could not get it to work properly. I need something like a VLOOKUP, but with multiple possible results and no duplicates. I need to make this work for users of Excel 2016 and 2019, so the UNIQUE and XLOOKUP functions in O365 are out. Additionally, it needs to work on desktop apps AND in Excel on the Web, so as far as I know VBA is out. I can do this easily in Access, but for user availability across platforms, we need to keep it in Excel, and I do not want to go through the trouble of making a custom interface. I have checked previously answered questions, but none seem to answer my question of having multiple levels of drop-downs without the need to create LOTS of individual tables.