Need help with dynamic dependent drop down list...

[soultaker]

Hey Guys!

I'm trying to make a excel sheet that has one array of options depending on another array...

I want to creat two drop down list. List 1 is choosing from A&B, then depend on that choice, the second drop down list will have only A2 or B2 for you to choose(if you picked a1 then you can only choose a2-1 to a2-4 in the second drop down list).

I tried countif, but since it's 1 in many, it always gives me result like (0,1,0,0), then it won't show the full list of the second options. Also tried MATCH, but if it didn't return any true result on the first IF, it'll just give me N/A, wont go into next 

Any ideas would be great!

[#2]

@soultaker 

Here's an example I built a couple of months ago that displays different secondary drop downs depending upon a first choice. See if it points you in the right direction. It does make use of Dynamic Array functions and requires the most recent version of Excel.

[#3]

@mathetes hmmm says file format or extention is not valid... I'm using office 365... 

[#4]

@soultaker 

Woops. Try this. I see that the last one only shows as a 1KB file. This is more complete.

[#5]

@soultaker 

Kindly follow these sequential steps: 

Step 1: Arrange the layout of your list, like this: 

Step 2: With A1 selected, define List1 (Ctrl+Alt+F3) with this formula: 

=List!$A$1:INDEX(List!$A:$A,COUNTA(List!$A:$A))

Step 3: With B1 selected, define ListHeaders (Ctrl+Alt+F3) with this formula: 

=List!$B$1:INDEX(List!$1:$1,COUNTA(List!$1:$1))

Step 4: With B2 selected, define ListData (Ctrl+Alt+F3) with this formula: 

=List!$B$2:INDEX(List!$B:$XFD,1048576,COUNTA(ListHeaders))

Step 5: Arrange your drop-down lists, like this: 

Step 6: With A2 selected, validate List1 (Alt,A,V,V), like this: 

Step 7: With B2 selected, define ListCount (Ctrl+Alt+F3) with this formula: 

=COUNTA(INDEX(ListData,0,MATCH(!A2,ListHeaders,0)))

Step 8: With B2 still selected, define CountedList (Ctrl+Alt+F3) with this formula: 

=INDEX(ListData,1,MATCH(!A2,ListHeaders,0)):INDEX(ListData,ListCount,MATCH(!A2,ListHeaders,0))

Step 9: With B2 still selected, validate List2 (Alt,A,V,V), like this: 

Step 10: With C2 selected, validate List3 (Alt,A,V,V), like this: 

Your validated lists will then look like this: 

Note that you can only select an item from List 1, if List2 is empty and List3 is empty. Moreover, you can only select an item from List2, if List1 is not empty and List3 is empty. Finally, you can only select an item from List3, if List1 is not empty and List2 is not empty. 

As you might have observed, the foregoing statements are the English Translations of the formulas in the Source box of the Data Validation tab.

[#6]

@soultaker 

As variant:

Let define ListA as

="a"& SEQUENCE(Dimension)

and SublistsA as

=$B4&"-"&SEQUENCE(1,Dimension)

Same for B.

Data validation for list

and for sublist

[#7]

@Twifoo Thank you!!

[#8]

@Sergei Baklan Thanks a lot!!