How can I create multiple dependent drop down lists

Lycias I realize this is an old discussion, but in case you or anyone else is interested in trying a LAMBDA based solution for creating dynamic, multi-level dependent drop-down lists, please see the attached workbook...

Open Name Manager to view the custom LAMBDA functions and named ranges. Basically, the TBL.TRANSFORM and TBL.JOINCOLS functions are used to transform your national areas table into a single lookup range with three columns: List_ID, Parent_ID and List_Items. The Parent_ID for each List_Item is created by joining all of the previous items for each record with a delimiter. These two named ranges are then passed to the third function, GET.LIST, which is used directly as the data validation list source.

The first data validation level (non-dependent) uses the first two arguments only:

=GET.LIST(List_Items, Parent_ID)

All other dependent data validation levels can be set together using the same syntax, with a mixed cell reference for the [criteria] argument. For example:

=GET.LIST(List_Items, Parent_ID, $A2:A2, " | ", No_Match)

For reference, the three custom LAMBDA functions are defined as follows:

TBL.TRANSFORM:
=LAMBDA(array,delimiter,[if_empty],
SORT(DROP(REDUCE(0, SEQUENCE(COLUMNS(array)), LAMBDA(v,n, LET(
   a, UNIQUE(CHOOSECOLS(array, SEQUENCE(n))),
   b, FILTER(a, TAKE(a,, -1)<>0, if_empty),
   h, ROWS(b),
   VSTACK(v, HSTACK(EXPAND(n, h,, n), IF(n=1, EXPAND("φ", h,, "φ"),
      TBL.JOINCOLS(TAKE(b,, n-1), delimiter)), TAKE(b,, -1)))))), 1), {1,2,3}))

TBL.JOINCOLS:
=LAMBDA(array,delimiter,
TEXTAFTER(REDUCE("", SEQUENCE(COLUMNS(array)), LAMBDA(v,n,
   v&delimiter&CHOOSECOLS(array, n))), delimiter))

GET.LIST:
=LAMBDA(list_range,criteria_range,[criteria],[delimiter],[if_empty], LET(
   v, IF(ISOMITTED(criteria), "φ", TEXTJOIN(delimiter, 0, criteria)),
   XLOOKUP(v, criteria_range, list_range, if_empty):
      XLOOKUP(v, criteria_range, list_range, if_empty,, -1)))

These functions are dynamic and can transform a table of any dimensions. Please see the attached workbook for a complete demonstration...

Lycias 

This might add something new to the mix.  It uses any current selection to filter other validation columns, so it can only be applied to a single combined selection.  On the other hand, it prevents the user from selecting a new country without first deleting the region.  An interesting characteristic of the validation occurs if 'Touba' is selected as a sub-region.  Returning to the country, shows both 'Cote d'Ivoire' and 'Senegal' to be valid.  'Northern' similarly has multiple occurrences.

Lycias 

Here's an example of 3 level dependent data-validation using the new function in Excel 365. The functions used are: SORT, UNIQUE, FILTER, XLOOKUP & TEXTSPLIT

https://www.mrexcel.com/board/threads/3-or-more-dependent-data-validation-with-vba-easy-to-set-up.1185808/post-6010313

Lycias 

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):

1. Prepare your data:
• Column A: Countries
• Column B: Provinces
• Column C: Districts
2. 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.
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: =CountryList.
• 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: =INDIRECT("ProvinceList").
• 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: =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.