Problem with linked list

[anglais428]

Hi all,

I have a problem with the following and was wondering is there anyway to get around it?

Cell A1: A list of countries (data validation source D2:D20)
Cell A2: A list of cities (data validation =Indirect(A1) ie only the cities within the selected country are displayed.
Cell A3: A vlookup based on A2
Cell A4: A vlookup based on A2

THis works fine when I select a location (say "UK") in A1, all UK cities are displayed in the dropdown in A2. Say I select London, the relevant data for London is then displayed in A3 and A4.

My problem is when I change the Country (cell A1) say from UK to USA, "London" still remains in cell A2 (even though the dropdown will change and display only USA cities).

Is there anyway to remove the selected city (ie make A2 blank) as soon as a new country (cell A1) is selected?

Thanks

 

[Yard]

OK, can you put it back in, then change something on the worksheet and tell me if a message appears.