How can I create multiple dependent drop down lists

Gold Contributor

May 24, 2023

To create a unique country drop-down list that dynamically filters the provinces and districts based on the selected country and province, you can use a combination of named ranges, the INDIRECT function, and the INDEX function.

Here's how you can set it up (without having opened the file, for personal security reasons):

Prepare your data:

Column A: Countries
Column B: Provinces
Column C: Districts

Define named ranges:

Select the range of countries (excluding the header) in Column A.
Go to the "Formulas" tab, click on "Define Name" in the "Defined Names" group.
In the "New Name" dialog box, enter a name for the range, e.g., "CountryList".
Repeat the above steps for the ranges of provinces and districts, naming them "ProvinceList" and "DistrictList" respectively.

Set up the first drop-down list for countries:

Select the cell where you want the country drop-down list to appear (e.g., cell E2).
Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
In the "Source" field, enter the formula: =CountryList.
Click "OK" to close the dialog box.

Set up the dependent drop-down list for provinces:

Select the cell where you want the province drop-down list to appear (e.g., cell F2).
Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
In the "Source" field, enter the formula: =INDIRECT("ProvinceList").
Click "OK" to close the dialog box.

Set up the dependent drop-down list for districts:

Select the cell where you want the district drop-down list to appear (e.g., cell G2).
Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
In the "Source" field, enter the formula: =INDEX(DistrictList,MATCH(G2,ProvinceList,0)). This formula uses the INDEX and MATCH functions to retrieve the districts that match the selected province.
Click "OK" to close the dialog box.

Now, when you select a country in cell E2, the province drop-down list in cell F2 will update based on the selected country. Similarly, when you select a province, the district drop-down list in cell G2 will update accordingly.

Make sure to adjust the cell references and named ranges according to your specific worksheet.

By following these steps, you can create a unique country drop-down list with filtered provinces and districts based on the selected country and province.

Jthour
Copper Contributor
Jan 24, 2024
This was EXACTLY what I was looking for!
Lycias
Brass Contributor
May 24, 2023
Thanks for this - I think this is close to what I am looking for. However, the first drop-down is not giving me a unique list. Please see the sample data pasted below (I am interested in ColB, D, and F) - when I do drop down for ColB the countries are repeated:
ColA ColB ColC ColnD ColE ColF
BDI Burundi BDI_1_1 Bubanza BDI_2_1 Bubanza NA
BDI Burundi BDI_1_1 Bubanza BDI_2_2 Mpanda NA
BDI Burundi BDI_1_16 Rumonge BDI_2_41 Bugarama NA
BDI Burundi BDI_1_16 Rumonge BDI_2_42 Rumonge NA
BDI Burundi BDI_1_8 Karusi BDI_2_20 Buhiga NA
BDI Burundi BDI_1_8 Karusi BDI_2_21 Nyabikere NA
BDI Burundi BDI_1_2 Bujumbura Mairie BDI_2_3 Bujumbura centre NA
BDI Burundi BDI_1_2 Bujumbura Mairie BDI_2_4 Bujumbura nord NA
BDI Burundi BDI_1_2 Bujumbura Mairie BDI_2_5 Bujumbura sud NA
BDI Burundi BDI_1_6 Cibitoke BDI_2_13 Bukinanyana NA
BDI Burundi BDI_1_6 Cibitoke BDI_2_14 Cibitoke NA
BDI Burundi BDI_1_6 Cibitoke BDI_2_15 Mabayi NA
BDI Burundi BDI_1_4 Bururi BDI_2_9 Bururi NA
BDI Burundi BDI_1_4 Bururi BDI_2_10lj Matana NA
BDI Burundi BDI_1_4 Bururi BDI_2_48gx Rutovu NA
BDI Burundi BDI_1_10 Kirundo BDI_2_25 Busoni NA
BDI Burundi BDI_1_10 Kirundo BDI_2_26 Kirundo NA
BDI Burundi BDI_1_10 Kirundo BDI_2_27 Mukenke NA
BDI Burundi BDI_1_10 Kirundo BDI_2_28 Vumbi NA
BDI Burundi BDI_1_18 Ruyigi BDI_2_45 Butezi NA
BDI Burundi BDI_1_18 Ruyigi BDI_2_49lk Gisuru NA
BDI Burundi BDI_1_18 Ruyigi BDI_2_46ib Kinyinya NA
BDI Burundi BDI_1_18 Ruyigi BDI_2_47 Ruyigi NA
BDI Burundi BDI_1_15 Ngozi BDI_2_38 Buye NA
BDI Burundi BDI_1_15 Ngozi BDI_2_39 Kiremba NA
BDI Burundi BDI_1_15 Ngozi BDI_2_40 Ngozi NA
BDI Burundi BDI_1_5 Cankuzo BDI_2_11 Cankuzo NA
BDI Burundi BDI_1_5 Cankuzo BDI_2_12 Murore NA
BDI Burundi BDI_1_14 Mwaro BDI_2_36 Fota NA
BDI Burundi BDI_1_14 Mwaro BDI_2_37 Kibumbu NA
BDI Burundi BDI_1_9 Kayanza BDI_2_22 Gahombo NA
BDI Burundi BDI_1_9 Kayanza BDI_2_23 Kayanza NA
BDI Burundi BDI_1_9 Kayanza BDI_2_24 Musema NA
BDI Burundi BDI_1_13 Muyinga BDI_2_33 Gashoho NA
BDI Burundi BDI_1_13 Muyinga BDI_2_34 Giteranyi NA
BDI Burundi BDI_1_13 Muyinga BDI_2_35 Muyinga NA
BDI Burundi BDI_1_17 Rutana BDI_2_43 Gihofi NA
BDI Burundi BDI_1_17 Rutana BDI_2_44 Rutana NA
BDI Burundi BDI_1_7 Gitega BDI_2_16 Gitega NA
BDI Burundi BDI_1_7 Gitega BDI_2_17 Kibuye NA
BDI Burundi BDI_1_7 Gitega BDI_2_18 Mutaho NA
BDI Burundi BDI_1_7 Gitega BDI_2_19 Ryansoro NA
BDI Burundi BDI_1_3 Bujumbura BDI_2_6 Isale NA
BDI Burundi BDI_1_3 Bujumbura BDI_2_7 Kabezi NA
BDI Burundi BDI_1_3 Bujumbura BDI_2_8 Rwibaga NA
BDI Burundi BDI_1_12 Muramvya BDI_2_31 Kiganda NA
BDI Burundi BDI_1_12 Muramvya BDI_2_32 Muramvya NA
BDI Burundi BDI_1_11 Makamba BDI_2_29 Makamba NA
BDI Burundi BDI_1_11 Makamba BDI_2_30 Nyanza-Lac NA
BEN Benin BEN_1_2ys Alibori BEN_2_6tl Banikoara NA
BEN Benin BEN_1_2ys Alibori BEN_2_4qt Kandi-Gogounou-Segbana NA
BEN Benin BEN_1_2ys Alibori BEN_2_20er Malanville-Karimama NA
BEN Benin BEN_1_6yg Atacora BEN_2_33xw Kouande-Pehunco-Kerou NA
BEN Benin BEN_1_6yg Atacora BEN_2_14ox Natitingou-Boukoumbe-Toucountouna NA
BEN Benin BEN_1_6yg Atacora BEN_2_1rx Tanguieta-Cobly-Materi NA
BEN Benin BEN_1_10oz Atlantique BEN_2_8bi Abomey-Calavi-So-ava NA
BEN Benin BEN_1_10oz Atlantique BEN_2_12mj Allada-Toffo NA
BEN Benin BEN_1_10oz Atlantique BEN_2_17pq Ouidah-Kpomasse-Tori-bossito NA
BEN Benin BEN_1_12ct Borgou BEN_2_18nm Bembereke-Sinende NA
BEN Benin BEN_1_12ct Borgou BEN_2_13tl N'Dali-Parakou NA
BEN Benin BEN_1_12ct Borgou BEN_2_15dk Nikki-Kalale-Perere NA
BEN Benin BEN_1_12ct Borgou BEN_2_10ez Tchaourou NA
BEN Benin BEN_1_7ra Collines BEN_2_24ac Dassa-Glazoue NA
BEN Benin BEN_1_7ra Collines BEN_2_7cr Savalou-Bante NA
BEN Benin BEN_1_7ra Collines BEN_2_2hm Save-Ouesse NA
BEN Benin BEN_1_1gz Couffo BEN_2_3ur Aplahoue-Djakotomey-Dogbo NA
BEN Benin BEN_1_1gz Couffo BEN_2_29lu Klouekanme-Toviklin- Lalo NA
BEN Benin BEN_1_9dz Donga BEN_2_25jj Bassila NA
BEN Benin BEN_1_9dz Donga BEN_2_31il Djougou-Ouake-Copargo NA
BEN Benin BEN_1_3xj Littoral BEN_2_27bg Cotonou 1 Et 4 NA
BEN Benin BEN_1_3xj Littoral BEN_2_11dt Cotonou 2 Et 3 NA
BEN Benin BEN_1_3xj Littoral BEN_2_30sn Cotonou 5 NA
BEN Benin BEN_1_3xj Littoral BEN_2_23kp Cotonou 6 NA
BEN Benin BEN_1_11oz Mono BEN_2_28bd Come-Grand-popo-Houeyogbe NA
BEN Benin BEN_1_11oz Mono BEN_2_22ud Lokossa-Athieme NA
BEN Benin BEN_1_8fr Oueme BEN_2_9cg Adjohoun-Bonou-Dangbo NA
BEN Benin BEN_1_8fr Oueme BEN_2_21nm Akpro-misserete-Avrankou-Adjarra NA
BEN Benin BEN_1_8fr Oueme BEN_2_26dh Porto-novo-Seme-podji-Aguegues NA
BEN Benin BEN_1_4qu Plateau BEN_2_34gg Pobe-Ketou-Adja-ouere NA
BEN Benin BEN_1_4qu Plateau BEN_2_5uu Sakete-Ifangni NA
BEN Benin BEN_1_5vw Zou BEN_2_16oq Bohicon-Za-kpota-Zogbodomey NA
BEN Benin BEN_1_5vw Zou BEN_2_19lc Cove-Zagnanado-Ouinhi NA
BEN Benin BEN_1_5vw Zou BEN_2_32jt Djidja-Abomey-Agbangnizoun NA
- NikolinoDE
  Gold Contributor
  May 24, 2023
  Lycias
  Maybe you can follow these modified steps:
  Prepare your data:
  Column B: Countries
  Column 😧 Provinces
  Column F: Districts
  Define named ranges:
  Select the range of countries (excluding the header) in Column B.
  Go to the "Formulas" tab, click on "Define Name" in the "Defined Names" group.
  In the "New Name" dialog box, enter a name for the range, e.g., "CountryList".
  Set up the first drop-down list for countries:
  Select the cell where you want the country drop-down list to appear (e.g., cell E2).
  Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
  In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
  In the "Source" field, enter the formula: "=UNIQUE(B2:B)".
  Click "OK" to close the dialog box.
  Set up the dependent drop-down list for provinces:
  Select the cell where you want the province drop-down list to appear (e.g., cell F2).
  Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
  In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
  In the "Source" field, enter the formula: "=IF(E2<>"", UNIQUE(FILTER(D2:D, B2:B=E2)),"")".
  Click "OK" to close the dialog box.
  Set up the dependent drop-down list for districts:
  Select the cell where you want the district drop-down list to appear (e.g., cell G2).
  Go to the "Data" tab and click on "Data Validation" in the "Data Tools" group.
  In the "Data Validation" dialog box, select "List" in the "Allow" drop-down list.
  In the "Source" field, enter the formula: "=IF(F2<>"", UNIQUE(FILTER(F2:F, D2:D=F2)),"")".
  Click "OK" to close the dialog box.
  Now, when you select a country in the country drop-down list, the provinces will dynamically change based on the selected country. Similarly, when you select a province, the districts will adjust accordingly.
  Make sure to adjust the ranges and references in the formulas to match your actual data range.
  - Lycias
    Brass Contributor
    May 24, 2023
    NikolinoDE, thanks again - when I do that for the first drop-down list, I get this error "The source currently evaluates to an error. Do you want to continue" from the data validation window.

Forum Discussion

How can I create multiple dependent drop down lists

Resources