dependent dropdown lists for different dropdown subsets from a primary list

Question

Hi. I have finally figured out how to create a dependent dropdown list, BUT I have the following situation. I have a list of drugs that have been approved for use for a select list of indication HOWEVER, not all of these drugs are approved for every indication, so I wanted to only list the drugs choices in a dropdown (various subsets of the parent list) available for each specific indication, and THEN list the brands available for each drug option. The list of brands for each drug is fixed - if the drug is approved for an indication, then all brands are covered, but not all the drugs are approved for every indication. For the first dropdown I had to create 5 different subset lists and enter the respective subset for each indication.

Indication Approved Drugs (primary drop downs)

1 A B C

2 B

3 A C

4 A B C

5 B C

Drug Brands (same/fixed number of brands for each drug; want to create a secondary drop down)

A A1 A2 A3

B B1 B2 B3 B4

C C1 C2

I appreciate that this may not be possible using a dependent drop down function as it appears that I need a singular primary drop down with unique items in order to create a secondary drop down. Please advise if there is a way to structure this differently but accomplish the same outcome in the same logical sequence.

Answer 1

You can use name range, then the data validation list source:

Column B

=IF(A1=1,INDIC1,IF(A1=2,INDIC2,IF(A1=3,INDIC3,IF(A1=4,INDIC4,IF(A1=5,INDIC5)))))

Column C

=IF(B1="A",DRUG1,IF(B1="B",DRUG2,IF(B1="C",DRUG3)))

You can check the file via private message by clicking the icon below.