Cascading dropdown lists from dynamic ranges

[devunow]

I wish to create a cascading dropdown list of towns dependent on which country is chosen. (as below)
The lists of towns varies and I wish the ranges on this to be dynamic.
At present I use: =OFFSET(Lists!$D$2:$D$2,0,0,MATCH("*",Lists!$d:$d,-1),1) to calculate the ranges.
The secod dropdown list does not work with the usual INDIRECT function. Is there something else I need to do because the lists are dynamic?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Countires		Canada	USA	UK	Spain	Italy	France		Dropdown Lists Below:
2	Canada		Ottawa	New York	London	Barcelona	Milan	Marseilles		Country	City
3	USA		Toronto	Chicago	Manchester	Madrid	Venice	Nice		UK
4	UK		Vancouver	Miami	Glasgow	Valencia	Rome	Paris
5	Spain			Seattle			Turin
6	Italy
7	France
Lists

 

[devunow]

Ah, have worked it out!

If you define a name using EVALUATE it will work with dynamic ranges. Choose Insert, Name, Define from the menu, enter List2 (or whatever name you care to choose) and in the refers to box enter this:
=EVALUATE(Sheet1!$J$3)
adjusting the sheet name if required. Then use =List2 as the list source for the second data validation.

 

[devunow]

Oh dear, though the evaluate function works, I need to define a new list for every dropdown menu I create - is there an easier way?

 

[bphirarak]

Suppose you name Countries = $A$2:$A$7, then, I normally use two methods:

1. Naming each city list as dynamic named range, such as Canada = OFFSET($C$2,0,0,COUNTA($C$2:$C$1000),1) and USA = OFFSET($D$2,0,0,COUNTA($D$2,$D$1000),1) and so on. Then, naming CityList = CHOOSE(MATCH($j$3,Countries,0),Canada,USA,UK,Spain,Italy,France). Finally, in K3, create the validation list with =CityList in source: box.

2. If you are not prefer to name each list of city, define name Countries as above, then I name the CityList

=OFFSET($B$2,0,MATCH($J$3,Countries,0),COUNTA(OFFSET($B$2,0,MATCH($J$3,Countries,0),1000,1)),1)

and create validation list at K3 using name CityList at source box.

Hope it can help.

 

[Andrew Poulsom]

Also look here:

http://www.contextures.com/xlDataVal02.html