Drop Down List with Filter and the Filter list in one single column

This will tell you what to do. It looks a bit daunting, at first sight.... but persevere!!
http://www.contextures.com/xlDataVal02.html

This will tell you what to do. It looks a bit daunting, at first sight.... but persevere!!
http://www.contextures.com/xlDataVal02.html

Here's one way...

Create a 2 column list of the countries and the associated cities...

Data Range

	A	B
1	All Country	City
2	USA	Boston
3	USA	New York
4	USA	Pittsburgh
5	USA	Dallas
6	Canada	Toronto
7	Canada	Montreal
8	Canada	Sault Ste Marie
9	Canada	Winnipeg
10	England	London
11	England	Cambridge
12	England	Brighton
13	England	Oxford

Now, create a list of the unique countries...

Data Range

	A	B	C
1	All Country	City	Unique Country
2	USA	Boston	USA
3	USA	New York	Canada
4	USA	Pittsburgh	England
5	USA	Dallas
6	Canada	Toronto
7	Canada	Montreal
8	Canada	Sault Ste Marie
9	Canada	Winnipeg
10	England	London
11	England	Cambridge
12	England	Brighton
13	England	Oxford

This array formula** entered in C2:

=IFERROR(INDEX(A$2:A$13,MATCH(0,COUNTIF(C$1:C1,A$2:A$13),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

Then, as the source for the country drop down list use this formula:

=OFFSET(C2,,,COUNTIF(C2:C100,"?*"))

Let's assume that drop down list is in cell F2.

As the source for the city drop down list use this formula:

=OFFSET(A2,MATCH(F2,A2:A13,0)-1,1,COUNTIF(A2:A13,F2))

Here's a sample file that demonstrates this...

That is way cool, Tony.

I've always tried INDEX / MATCH for my dynamic ranges in drop downs. Of course they don't work.

Thanks for this. It's in my "Keeper" file, now.

Rep added.

Thanks!

You're welcome. Thanks for the feedback!