Forum Discussion

Lycias's avatar
Lycias
Brass Contributor
May 23, 2023

How can I create multiple dependent drop down lists

I want to create multiple dependent drop-down lists from the file below. When I select a country in column B I want only entries from column D for that country to appear in the next drop-down list, a...
excel
Lycias's avatar
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's avatar
NikolinoDE
Gold Contributor
May 24, 2023

Lycias 

Maybe you can follow these modified steps:

  1. Prepare your data:
    • Column B: Countries
    • Column 😧 Provinces
    • Column F: Districts
  2. 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".
  3. 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.
  4. 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.
  5. 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.

Resources