Multiple Conditional Data Validations

[Nathan Asius]

I am attempting to insert various Validation lists in Cell D6 based upon Multiple conditions.
I have a Data Validation List from Table 1 in C3. And another Data Validation List from Table 2 in D6.
Whichever of the multiple AND conditions appear from those many permutations I would like any of the specific Data Validation Lists Defined with the multiple range names on the right show up.

I was beginning to enter in a very long IF(AND nested formula in my List box for cell D6 until I realized how long it would be before I lost track. I tried mapping it out on paper and speculated there must be a better way.
I looked up using Case Statements (VBA) realizing it is not ideal for this task either.

Any suggestions on the most efficient way to do this? Excel 365.

Thanks,
Nathan

 

[dreid1011]

I understand the concept of what you are trying to do. I suppose what I don't understand is how the first two dropdowns = the third. Per your picture, how are you getting column I from the values 2 and 2B?

 

[Nathan Asius]

I understand the concept of what you are trying to do. I suppose what I don't understand is how the first two dropdowns = the third. Per your picture, how are you getting column I from the values 2 and 2B?

 

[dreid1011]

This is what I understand you to want, or at least something similar. With the values entered as "9" (value 1) and "G" (value 2), that gave a result of "G9" as I understood from your original formula. Then "G9" is interpreted to reference the named range "G_9" and returns the values in the "G_9" named range. But your most recent image doesn't make logical sense how you got column I with the entered values.

 

[Nathan Asius]

The lists shown in the sample and my actual workbook, are limited and pre-defined. There won't be any user input to make these dynamic as well as dependent. So every list/named range already exists in their entirety. These only have to dropdown in cell C16 based upon the user's selection in B16. And similarly, the dropdown in D16 is dependent upon what the user selects in the dropdown showing in D16. So the strings in the list in column I are not driven from anything prior.