Need help with dynamic dependent drop down list...

%3CLINGO-SUB%20id%3D%22lingo-sub-1955383%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20dynamic%20dependent%20drop%20down%20list...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1955383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22111111.png%22%20style%3D%22width%3A%20368px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237666i0260089BC474068A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22111111.png%22%20alt%3D%22111111.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHey%20Guys!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20a%20excel%20sheet%20that%20has%20one%20array%20of%20options%20depending%20on%20another%20array...%3C%2FP%3E%3CP%3EI%20want%20to%20creat%20two%20drop%20down%20list.%20List%201%20is%20choosing%20from%20A%26amp%3BB%2C%20then%20depend%20on%20that%20choice%2C%20the%20second%20drop%20down%20list%20will%20have%20only%20A2%20or%20B2%20for%20you%20to%20choose(if%20you%20picked%20a1%20then%20you%20can%20only%20choose%20a2-1%20to%20a2-4%20in%20the%20second%20drop%20down%20list).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20countif%2C%20but%20since%20it's%201%20in%20many%2C%20it%20always%20gives%20me%20result%20like%20(0%2C1%2C0%2C0)%2C%20then%20it%20won't%20show%20the%20full%20list%20of%20the%20second%20options.%20Also%20tried%20MATCH%2C%20but%20if%20it%20didn't%20return%20any%20true%20result%20on%20the%20first%20IF%2C%20it'll%20just%20give%20me%20N%2FA%2C%20wont%20go%20into%20next%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20ideas%20would%20be%20great!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1955383%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1956124%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20dynamic%20dependent%20drop%20down%20list...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1956124%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890861%22%20target%3D%22_blank%22%3E%40soultaker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20an%20example%20I%20built%20a%20couple%20of%20months%20ago%20that%20displays%20different%20secondary%20drop%20downs%20depending%20upon%20a%20first%20choice.%20See%20if%20it%20points%20you%20in%20the%20right%20direction.%20It%20does%20make%20use%20of%20Dynamic%20Array%20functions%20and%20requires%20the%20most%20recent%20version%20of%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1957114%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20dynamic%20dependent%20drop%20down%20list...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1957114%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3Bhmmm%20says%20file%20format%20or%20extention%20is%20not%20valid...%20I'm%20using%20office%20365...%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1957365%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20dynamic%20dependent%20drop%20down%20list...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1957365%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890861%22%20target%3D%22_blank%22%3E%40soultaker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWoops.%20Try%20this.%20I%20see%20that%20the%20last%20one%20only%20shows%20as%20a%201KB%20file.%20This%20is%20more%20complete.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1958715%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20dynamic%20dependent%20drop%20down%20list...%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1958715%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F890861%22%20target%3D%22_blank%22%3E%40soultaker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20follow%20these%20sequential%20steps%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%201%3C%2FSTRONG%3E%3A%20Arrange%20the%20layout%20of%20your%20list%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22List%20Layout.PNG%22%20style%3D%22width%3A%20530px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237962iE8F46BF181CCA7B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22List%20Layout.PNG%22%20alt%3D%22List%20Layout.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%202%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EA1%3C%2FSTRONG%3E%20selected%2C%20define%20%3CSTRONG%3EList1%3C%2FSTRONG%3E%20(Ctrl%2BAlt%2BF3)%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DList!%24A%241%3AINDEX(List!%24A%3A%24A%2CCOUNTA(List!%24A%3A%24A))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%203%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EB1%3C%2FSTRONG%3E%20selected%2C%20define%20%3CSTRONG%3EListHeaders%3C%2FSTRONG%3E%20(Ctrl%2BAlt%2BF3)%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DList!%24B%241%3AINDEX(List!%241%3A%241%2CCOUNTA(List!%241%3A%241))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%204%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EB2%3C%2FSTRONG%3E%20selected%2C%20define%20%3CSTRONG%3EListData%3C%2FSTRONG%3E%20(Ctrl%2BAlt%2BF3)%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DList!%24B%242%3AINDEX(List!%24B%3A%24XFD%2C1048576%2CCOUNTA(ListHeaders))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%205%3C%2FSTRONG%3E%3A%20Arrange%20your%20drop-down%20lists%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Drop%20Lists.PNG%22%20style%3D%22width%3A%20220px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237964i4B134A233AE48313%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Drop%20Lists.PNG%22%20alt%3D%22Drop%20Lists.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%206%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EA2%3C%2FSTRONG%3E%20selected%2C%20validate%20%3CSTRONG%3EList1%3C%2FSTRONG%3E%20(Alt%2CA%2CV%2CV)%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22List1%20Validation.PNG%22%20style%3D%22width%3A%20625px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237966i1D5C688C88C2B470%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22List1%20Validation.PNG%22%20alt%3D%22List1%20Validation.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%207%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EB2%3C%2FSTRONG%3E%20selected%2C%20define%20%3CSTRONG%3EListCount%3C%2FSTRONG%3E%20(Ctrl%2BAlt%2BF3)%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DCOUNTA(INDEX(ListData%2C0%2CMATCH(!A2%2CListHeaders%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%208%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EB2%3C%2FSTRONG%3E%20still%20selected%2C%20define%20%3CSTRONG%3ECountedList%3C%2FSTRONG%3E%20(Ctrl%2BAlt%2BF3)%20with%20this%20formula%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(ListData%2C1%2CMATCH(!A2%2CListHeaders%2C0))%3AINDEX(ListData%2CListCount%2CMATCH(!A2%2CListHeaders%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%209%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EB2%3C%2FSTRONG%3E%26nbsp%3Bstill%20selected%2C%20validate%20%3CSTRONG%3EList2%3C%2FSTRONG%3E%20(Alt%2CA%2CV%2CV)%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22List2%20Validation.PNG%22%20style%3D%22width%3A%20686px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237974i85C1F62560B97262%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22List2%20Validation.PNG%22%20alt%3D%22List2%20Validation.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EStep%2010%3C%2FSTRONG%3E%3A%20With%20%3CSTRONG%3EC2%3C%2FSTRONG%3E%20selected%2C%20validate%20%3CSTRONG%3EList3%3C%2FSTRONG%3E%20(Alt%2CA%2CV%2CV)%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22List3%20Validation.PNG%22%20style%3D%22width%3A%20749px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237968iD363B8F582F4C0F7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22List3%20Validation.PNG%22%20alt%3D%22List3%20Validation.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20validated%20lists%20will%20then%20look%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Validated%20Lists.PNG%22%20style%3D%22width%3A%20309px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F237969i6CAE966831B478E3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Validated%20Lists.PNG%22%20alt%3D%22Validated%20Lists.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20you%20can%20only%20select%20an%20item%20from%20%3CSTRONG%3EList%201%3C%2FSTRONG%3E%2C%20if%20%3CSTRONG%3EList2%3C%2FSTRONG%3E%20is%20empty%20and%20%3CSTRONG%3EList3%3C%2FSTRONG%3E%20is%20empty.%20Moreover%2C%20you%20can%20only%20select%20an%20item%20from%20%3CSTRONG%3EList2%3C%2FSTRONG%3E%2C%20if%20%3CSTRONG%3EList1%3C%2FSTRONG%3E%20is%20not%20empty%20and%20%3CSTRONG%3EList3%3C%2FSTRONG%3E%20is%20empty.%20Finally%2C%20you%20can%20only%20select%20an%20item%20from%20%3CSTRONG%3EList3%3C%2FSTRONG%3E%2C%20if%20%3CSTRONG%3EList1%3C%2FSTRONG%3E%20is%20not%20empty%20and%20%3CSTRONG%3EList2%3C%2FSTRONG%3E%20is%20not%20empty.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20might%20have%20observed%2C%20%3CSTRONG%3Ethe%20foregoing%20statements%20are%20the%20English%20Translations%20of%20the%20formulas%20in%20the%20Source%20box%20of%20the%20Data%20Validation%20tab%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

111111.png

 

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!

 

 

7 Replies

@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.

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

@soultaker 

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

@soultaker 

Kindly follow these sequential steps: 

 

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

List Layout.PNG

 

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: 

Drop Lists.PNG

 

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

List1 Validation.PNG

 

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: 

List2 Validation.PNG

 

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

List3 Validation.PNG

 

Your validated lists will then look like this: 

Validated Lists.PNG

 

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.

@soultaker 

As variant:

image.png

Let define ListA as

="a"& SEQUENCE(Dimension)

and SublistsA as

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

Same for B.

Data validation for list

image.png

and for sublist

image.png