Generate a filtered, dynamic drop down list

Question

I need two dynamic drop down lists for data validation. One containing a unique list of continents to choose from, and then the second list which is a dynamically generated subset of countries based on the continent selected. The data is not in any particular order:

     A          B
---+--------------------
1  | Continent  Country
2  | Africa     Algeria
3  | Asia       China
4  | Africa     Ethiopia
5  | Europe     France
6  | Europe     Germany
7  | Asia       India
8  | Europe     Italy
9  | Asia       Japan
10 | Europe     Poland
11 | Africa     South Africa
12 | Europe     Spain

I have successfully created the first drop down list by using a hidden column to generate a unique list of continents and then associate them as a named range. So that part's done, however

how do I create a second dynamically generated, filtered list (preferably without any gaps in the list) based on the Continent association selected in the first list?

The actual data I'm digesting is thousands of data points large, so performance is a concern, and I'd prefer to not use VBA if possible.

Edit: With a bit more searching I found a link that was helpful, that provided me with this formula: IFERROR(INDEX($A$2:$A$100,SMALL(IF($B$2:$B$100="Yes",ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($A$2:$A2))),"")

It's closer, however it won't work since I'd need to put these in a separate column in my worksheet for every row where I need the dynamic drop down list, plus I'm unsure how large the filtered list will be.

Is there any way of doing this directly inside a named range?

Answer 1

If you sort the list ascending by continent, then you can build a dynamic range name without any helper cells, with the formula

=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E$2,Sheet1!$A:$A,1))

where cell E2 has the selected continent. Here is a screenshot with the same scenario. Replace Category with Continent and replace SubCategory with Country.