Anyone know how to make a dropdown list dependent on the contents of 2 other dropdowns?

%3CLINGO-SUB%20id%3D%22lingo-sub-97127%22%20slang%3D%22en-US%22%3EAnyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-97127%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20create%20a%20form%20with%20Province%2C%20City%2C%20District%20dropdowns%20being%20dependent%20on%20each%20other.%20I%20was%20able%20to%20create%20the%20dropdown%20for%20the%20provinces%20and%20make%20city%20dropdown%20only%20populate%20cities%20within%20the%20selected%20province%2C%20but%20when%20it%20comes%20to%20districts%20in%20the%26nbsp%3Bprovince%2Fcity%20combination%20i%20cant%20seem%20to%20get%20results.%20Can%20anyone%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-97127%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20%26amp%3B%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETutorial%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1120497%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1120497%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20stumbled%20across%20this%20post%20from%20over%20two%20years%20ago%20and%20it%20fits%20an%20issue%20I%20am%20trying%20to%20resolve%20as%20well.%20I%20followed%20the%20listing.xls%20file%20you%20posted%20but%20the%20dropdown%20in%20column%20C%20does%20not%20appear%20to%20be%20working.%20Something%20seems%20to%20be%20wrong%20with%20the%20data%20validation%20in%20that%20column%20as%20none%20of%20the%20Barangay%20dropdowns%20work.%20Do%20you%20know%20what%20needs%20to%20be%20fixed%20in%20the%20validation%20formula%20for%20this%20dropdown%3F%20Id%20like%20to%20apply%20the%20concept%20to%20a%20Dependent%20Validation%20drop%20down%20table%20I%20have%20setup%20but%20cant%20get%20it%20to%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-97588%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-97588%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Francis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20continue%20with%20your%20data%20structure%2C%20the%20only%20uses%20your%20Tables%20instead%20of%20ranges%20like%20%24A%3A%24A%20-%20better%20at%20least%20from%20performance%20point%20of%20view.%3C%2FP%3E%3CP%3EInstaed%20of%20Table1%60%2C..%20they%20are%20renamed%20as%20Province%2C%20ProvinceCity%20and%20AllData.%3C%2FP%3E%3CP%3EName%20of%20the%20used%20columns%20are%20added%20to%20list%20of%20names%20(see%20in%20Names%20Manager)%2C%20references%20as%20here%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20289px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F18797iFCBF864E3547A1F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Names.JPG%22%20title%3D%22Names.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThus%20for%20the%20first%20list%20(Province)%20the%20formula%20is%3C%2FP%3E%3CPRE%3E%3DINDIRECT(ProvinceList)%3C%2FPRE%3E%3CP%3E(to%20work%20with%20tables%20we%20have%20to%20use%20INDIRECT%2C%20structured%20references%20don't%20work%20within%20list%20formulas)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%20one%20for%20the%20City%20which%20depends%20on%20Province%20is%20actually%20as%20yours%2C%20only%20in%20tables%20notation%3C%2FP%3E%3CPRE%3E%3DOFFSET(Lists!%24D%241%2CMATCH(A2%2CINDIRECT(PCProvince)%2C0)%2C1%2CCOUNTIF(INDIRECT(PCProvince)%2CA2)%2C1)%3C%2FPRE%3E%3CP%3EFor%20the%20third%20list%20we%20shall%20filter%20selected%20Province%20and%20City%20what%20we%20do%20in%20MATCH%20with%20AND%20condition%3B%20and%20number%20of%20rows%20is%20calculated%20by%20COUNTIFS%20with%20two%20conditions%20(instaed%20of%20COUNTIF)%3C%2FP%3E%3CPRE%3E%3DOFFSET(Lists!%24G%241%2CMATCH(1%2C(INDIRECT(AllProvince)%3DA2)*(INDIRECT(AllCity)%3DB2)%2C0)%2C2%2CCOUNTIFS(INDIRECT(AllProvince)%2CA2%2CINDIRECT(AllCity)%2CB2)%2C1)%3C%2FPRE%3E%3CP%3EThe%20file%20is%20attached%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-97496%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-97496%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Francis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOkay%2C%20i%20see%20the%20point.%20Will%20play%20with%20this%20bit%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-97442%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-97442%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergei!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20quick%20response!%20I%20tried%20this%20method%20but%20this%20doesnt%20seem%20scalable%20to%20large%20quantities%20of%20data%20as%20i%20would%20have%20to%20make%20multiple%20verticals%20per%20state.%20Im%20actually%20working%20on%20the%20entire%20philippines%20and%20have%20attached%20the%20sample%20data%20to%20help%20clarify%26nbsp%3Bwould%20you%20happen%20to%20know%20of%20any%20alternative%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-97167%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-97167%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Francis%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20an%20example%20with%203%20dependent%20lists%20(Country%2C%20Region%2C%20City)%2C%20i%20guess%20close%20to%20your%20case%3C%2FP%3E%3CP%3E%3CA%20href%3D%22http%3A%2F%2Fwww.contextures.com%2FxlDataVal02.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttp%3A%2F%2Fwww.contextures.com%2FxlDataVal02.html%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121394%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121394%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527209%22%20target%3D%22_blank%22%3E%40Trevelyan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20last%203%20cells%20I%20use%20named%20formula%20for%20data%20validation%20and%20it%20works.%20For%20thirst%203%20it%20is%20direct%20formula%20and%20it%20doesn't%20work%20after%20re-opening.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20211px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166424i0643065828E78D81%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EWill%20play%20some%20more%20with%20that%2C%20but%20later.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516595%22%20slang%3D%22de-DE%22%3ESubject%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516595%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F72607%22%20target%3D%22_blank%22%3E%40Francis%20Cleofe%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22text-wrap%20tlid-copy-target%22%3E%3CDIV%20class%3D%22result-shield-container%20tlid-copy-target%22%3E%3CSPAN%20class%3D%22tlid-translation%20translation%22%3E%3CSPAN%20class%3D%22%22%3EMaybe%20this%20file%20helps%20too%20...%20had%20it%20in%20my%20archive%20...%20it%20was%20a%20long%20time%20ago%20...%20I%20think%20I%20downloaded%20it%20from%20a%20German%20forum%20(hmmm%20i%20dont%20remember%20it)%20...%20Anyway%20thanks%20to%20the%20donor%20...%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22%22%3Etry%20it%20maybe%20it%20will%20help%20you%20just%20as%20well%20...%20if%20not%20...%20delete%20it%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-result-transliteration-container%20result-transliteration-container%20transliteration-container%22%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22tlid-transliteration-content%20transliteration-content%20full%22%3ENikolino%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1121354%22%20slang%3D%22en-US%22%3ERe%3A%20Anyone%20know%20how%20to%20make%20a%20dropdown%20list%20dependent%20on%20the%20contents%20of%202%20other%20dropdowns%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1121354%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F527209%22%20target%3D%22_blank%22%3E%40Trevelyan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20are%20right%2C%20third%20column%20doesn't%20work%20properly.%20If%20open%20data%20validation%20settings%20and%20Ok%20list%20in%20the%20cell%20is%20shown%20for%20a%20while%2C%20after%20that%20stop%20working.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnyway%2C%20that's%20a%20bug%2C%20so%20far%20don't%20know%20there.%20Could%20you%20please%20say%20me%20are%20you%20on%20Excel%3B%20version%20with%20dynamic%20arrays%20or%20not%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I'm trying to create a form with Province, City, District dropdowns being dependent on each other. I was able to create the dropdown for the provinces and make city dropdown only populate cities within the selected province, but when it comes to districts in the province/city combination i cant seem to get results. Can anyone help?

10 Replies
Highlighted

Hi Francis,

 

Here is an example with 3 dependent lists (Country, Region, City), i guess close to your case

http://www.contextures.com/xlDataVal02.html

 

Highlighted

Hi Sergei!

 

Thanks for the quick response! I tried this method but this doesnt seem scalable to large quantities of data as i would have to make multiple verticals per state. Im actually working on the entire philippines and have attached the sample data to help clarify would you happen to know of any alternative?

Highlighted

Hi Francis,

 

Okay, i see the point. Will play with this bit later.

Highlighted

Hi Francis,

 

I continue with your data structure, the only uses your Tables instead of ranges like $A:$A - better at least from performance point of view.

Instaed of Table1`,.. they are renamed as Province, ProvinceCity and AllData.

Name of the used columns are added to list of names (see in Names Manager), references as here

 

Names.JPG

 

Thus for the first list (Province) the formula is

=INDIRECT(ProvinceList)

(to work with tables we have to use INDIRECT, structured references don't work within list formulas)

 

Next one for the City which depends on Province is actually as yours, only in tables notation

=OFFSET(Lists!$D$1,MATCH(A2,INDIRECT(PCProvince),0),1,COUNTIF(INDIRECT(PCProvince),A2),1)

For the third list we shall filter selected Province and City what we do in MATCH with AND condition; and number of rows is calculated by COUNTIFS with two conditions (instaed of COUNTIF)

=OFFSET(Lists!$G$1,MATCH(1,(INDIRECT(AllProvince)=A2)*(INDIRECT(AllCity)=B2),0),2,COUNTIFS(INDIRECT(AllProvince),A2,INDIRECT(AllCity),B2),1)

The file is attached

 

Highlighted

@Sergei Baklan I stumbled across this post from over two years ago and it fits an issue I am trying to resolve as well. I followed the listing.xls file you posted but the dropdown in column C does not appear to be working. Something seems to be wrong with the data validation in that column as none of the Barangay dropdowns work. Do you know what needs to be fixed in the validation formula for this dropdown? Id like to apply the concept to a Dependent Validation drop down table I have setup but cant get it to work.

Highlighted

@Trevelyan 

You are right, third column doesn't work properly. If open data validation settings and Ok list in the cell is shown for a while, after that stop working.

 

Anyway, that's a bug, so far don't know there. Could you please say me are you on Excel; version with dynamic arrays or not?

 

Thank you

Highlighted

@Trevelyan 

For last 3 cells I use named formula for data validation and it works. For thirst 3 it is direct formula and it doesn't work after re-opening.

image.png

Will play some more with that, but later.

Highlighted

@Sergei Baklan Thanks for the prompt reply. I am office 365 for the Excel version. Ill take a look at the updated file today. I did some searching yesterday on it and it appears Arrays cause problems in the data validation. They work while the file is open after you enter the formula. But if you save and close, then reopen the file, it no longer works. 

Highlighted

@Trevelyan 

Yes, exactly the same issue on some other files. But that's not only on Excel with Dynamic Arrays, I checked on semi-annual version (pre-DA Excel) - same story.

Highlighted

@Francis Cleofe 

Maybe this file helps too ... had it in my archive ... it was a long time ago ... I think I downloaded it from a German forum (hmmm i dont remember it) ... anyway thanks to the donor ... try it maybe it will help you just as well ... if not ... delete it
 
Nikolino