Dependent Hierarchical Drop Down List with more than 7 Nested IF Statements

[qanjorin]

Running into a bit of a snag. I used to have all of my data stored in one table and then used MATCH and INDEX to create my dependent drop down lists, but I needed a better way to sort through all of the data, as it is almost continually growing and is at least 750 items in total.

Now I have separated all of the data into 12 different tables (not actually formatted as a table) based on a subcategory which I named "SeriesName".

My first drop down is the title of the column which all of these tables share, dd_Formations. I want to have a drop down list that is dependent on what is shown in the dd_Formations cell.

First, it should show all of the items under SeriesName that exists because everything under dd_Formations contains these subcategories. Then, once I select this subcategory from SeriesName I want it only show the plays inside of that subcategory dependent on what is shown in the dd_Formations cell. I want it to open similar to a Windows Start Menu, where I make a selection, and a list of options under that selection appears....dependent on the aforementioned cell.

Basically, I need a drop down list inside of a drop down list in the same cell, only showing the second option selected, all dependent on the previous drop down list.

Hopefully that all makes sense. Attached is part of the Worksheet that I am trying to get this to work with.

[JohnTopley]

Take a look here ...

http://www.contextures.com/xlDataVal02.html

[qanjorin]

Not really what I'm looking for...thanks though. Please look at my worksheet that I uploaded to understand more of what I need. My drop down lists work fine, I just can't get the second option to appear in the same cell once I select the SeriesName.

[qanjorin]

Update: Realized I was referring to the wrong sheet in my formulas. For my formulas beginning with Plays I referred to the Tables sheet instead of the Calls sheet. I am now getting both options in my drop down; however it is not limiting to the SeriesName that is chosen. The list simply starts with the SeriesName chosen, but it includes every single cell that is underneath the Formation on the Tables sheet. Any way to fix that??