Dependent Data Validation with Dynamic Range

Using Excel 365

Hi,

First, thanks in advance for the help, and sorry for the long post. I am currently creating a document using dependent data validation. I have three categories of data that I am using. For this purpose let's say the first (header) category is countries, and the dependent category is cities. The last element is a large datasheet to find which cities should be included out of the given city list to make sure the data validation options aren't infinitely long, and also has information about the city that the validation spits out.

Initially, the sheet worked fine using name manager to name the array of each country's cities (which were not formula reliant) and then using the indirect function for the second data validation. However, now I am trying to make the sheet extremely easy to update because the list of applicable cities in each country is going to be changing. I want to be able to download that list of cities and drop it directly into my sheet.

Where I'm at currently is first, using the filter equation to sort the cities to each country, second using an Xlookup to see if the city is also present in the large dataset, and finally, a final filter equation to remove blanks from the Xlookup. At this point, I have a list of all the cities appropriated cleaned and sorted (both using an IF equation and Filter equation to see if that made a difference). However, I cannot figure out how to get these ranges to work in the data validation, noting that they are dynamic and will automatically be changing length.

What I've tried:

- Using an offset equation to name the range (the indirect equation in the validation does not seem to recognize this)

- creating a table for each country (this does not correctly auto-lengthen when I do it, possibly because of the equation I'm using to clean it? I'm not sure-- have tried with the data originating from =filter and =if. Also when trying to sort out blanks it shortens the entire sheet not just the specific table)

If anyone has tips or needs any clarification on how to make the dynamic ranges, I would hugely appreciate it. Thanks again!