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?

user_18108209 · Answer

I guess I am a gravedigger now but I was looking for the same thing and found this thread. All the different options were so hard to do and understand but I developed my own solution.

All it takes is: 6 formulas in total. One support column and one support sheet.

Here it is:

I based it on this table set up with continents and countries, from the original post. It has Continents and Countries in columns and is named "Countries". It is the table that holds all Continents and its respective countries. It is in Sheet1.

I then made a new sheet (Sheet2) with a new table ("Test") which had the following columns: Continent (to hold a drop down of the unique continents), Country (to hold a dropdown with the countries that belong to the continent chosen) and a support column (Order) that adds the order of the continent, in the unique list of continents. The formula of the support column is:

=MATCH([@Continent],UNIQUE(Countries[Continent]),0)

How the table "Test" looks now. It has 3 columns: Continent, Country and Order

Then I create a new sheet (Sheet3). In A1 I add this formula, to get all the unique Continents in a column: =UNIQUE(Countries[Continent])

In B1 I add the following formula: =IFERROR(TRANSPOSE(SORT(INDEX(Countries[Country],SMALL(IF(Countries[Continent]=$A1,ROW(Countries[Continent])-1),ROW(INDIRECT("1:"&COUNT(IF(Countries[Continent]=$A1,ROW(Countries[Continent])-1)))))))),"")

It finds all countries in table "Countries", that have the continent that shows up in column A of Sheet2 and fill them into the columns, in alphabetical order. I expand this formula into all rows of column B (or just as many as the number of choices that you need, with continents, 9 should suffice).

How it should look when sheet3 is set up.

When that is done I make a named range for all the continents. I name it "UniqueContinents" =INDIRECT("Sheet3!$A$1:$A$"&COUNTIF(Sheet3!$A:$A,"*"))

Now all that is left is to add the data validation.

In table "Test" in the column "Continent" I add the data validation: =UniqueContinents

In table "Test" in the column "Country" I add the data validation: =INDIRECT("Sheet3!"&ADDRESS($C2,2)&":"&ADDRESS($C2,COUNTIF(INDIRECT("Sheet3!"&$C2&":"&$C2),"*"))) It fetches the columns from Sheet3 on the row that matches the chosen Continent.

And I am done. If I add more Continents or Countries into the "Countries" table, all other dropdowns grow dynamically. If I delete something, it gets deleted everywhere.

This has been translated from the Swedish formulas so if something does not work properly, check that I have not missed the conversion. We use ; and not , as separators in formulas, for example.

The table with a working dropdown.

Generate a filtered, dynamic drop down list

5 Answers