Dec 04 2020 02:04 AM
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!
Dec 04 2020 05:51 AM
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.
Dec 04 2020 10:43 AM
@mathetes hmmm says file format or extention is not valid... I'm using office 365...
Dec 04 2020 11:58 AM
Woops. Try this. I see that the last one only shows as a 1KB file. This is more complete.
Dec 05 2020 12:57 AM - edited Dec 05 2020 01:23 AM
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.
Dec 05 2020 02:02 AM
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
Dec 06 2020 04:58 PM
@Sergei Baklan Thanks a lot!!