Dropdown based on another dropdown filter ?

Try attached
Datavalidation-Country-City.xlsx

Hi,

First create a NamedRange called "CountryList" with below formula:

=OFFSET(Sheet1!$B$3, 0, 0, COUNTA(Sheet1!$B$3:$B$100))

Select allOpen in new window

Then create a Unique List for Countries with below Array Formula confirmed with Ctrl+Shift+Enter in Col AA:

=IFERROR(INDEX(CountryList,MATCH(0,COUNTIF($AA$1:AA1,CountryList),0)),"")

Select allOpen in new window

Then create another NamedRange called "Countries" with below formula:

=Sheet1!$AA$2:$AA$4

Select allOpen in new window

Create First Dropdown on F3 with List as "Countries"
Select at least one country before you proceed to next step.
Then Create second dropdown on H3 with below formula:

=OFFSET($B$1,MATCH(F3,$B:$B,0)-1,1,COUNTIF($B:$B,F3),1)

Select allOpen in new window

Hope this helps, please find attached...
DependentDropdownLists_v1.xlsx

Hi

The two examples already provide work fine, provided your source list is sorted by Country.
If you add more entries out of order then it will fail.

I prefer to use Tables these days for data validation, with as many levels of nesting as you require.
The attached workbook shows an example with your data (I took the liberty of changing Scotland to Edinburgh, as Scotland is a country!!)

I have written tutorials on this with example files which can be found here  http://www.contextures.com/exceldatavaldependindextablesindirect.html
Sample--RJG-.xlsx

Just change the formula for NamedRange "Countries" from:

=Sheet1!$AA$2:$AA$4

Select allOpen in new window

To

=OFFSET(Sheet1!$AA$2, 0, 0, COUNT(IF(Sheet1!$AA$2:$AA$100="", "", 1)), 1)

Select allOpen in new window

Add as many countries and their cities you like.
DependentDropdownLists_v2.xlsx

@Shums

I agree a dynamic range deals with adding more countries.
That was not my point.
I was merely pointing out to the user, that adding further countries / cities would need to have the data sorted by country in order to work.

Hi Roger,

Here you go with Countries sorted list...
DependentDropdownLists_v3.xlsx

Hi Shums
I'm sorry, but you are still missing the point I was drawing to the attention of the OP.
I know how to sort data, I was merely pointing out that if he / she does add more data, then the list HAS to be sorted first, otherwise it doesn't work.

You don't need to sort the list, you can add as many countries and with their cities, Array formula will sort the list automatically.

In attached I added Australia and it works.
DependentDropdownLists_v3.xlsx

Hi Shums

I am sorry, but you are still failing to understand.
Of course if you go and add a series of entries for another country (Australia) at the end of the list, and add cities within that country one after the other then it will work without sorting.
But now, just decide that you need more cities in USA. Go and add USA and San Francisco to the end of your list, and USA Austin and then tell me that your method works without sorting.

It doesn't. You have to have sorted the two columns first, otherwise when you select USA as the country, you will get London and Cambridge appearing within your list and not the two you have just added.
The same applies for adding any other Country / City to the list, where the Country has already been added.