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

 

[anglais428]

That has changed the first city in the drop down to blank.
It seems to have deleted all the cities that just have one city in the dropdown :S

 

[anglais428]

I have put the follow macro on with a button entitled "new selection"

Sub ClearContents()
With ActiveSheet
.Range("A2").ClearContents
End With
Range("A1").Select
End Sub

This clears the cell but its just not ideal and it keeps the original country. Could this be modified so that when you click the button instead of moving to cell A1 it opens the dropdown list in cell A1?

 

[Yard]

Huh?? I'm really lost now. There's no way that code has "deleted all the cities that just have one city in the dropdown".

When you change the value of a cell in the worksheet, do you get the message box telling you "Something has changed"?

 

[anglais428]

Your code deleted the first city from each countries list

 

[Yard]

Where are the lists of cities for each country? On a different worksheet?

When you change the value of a cell in the worksheet with the dropdowns, do you get the message box telling you "Something has changed"?<!-- / message --><!-- sig -->

 

[anglais428]

The range of countries is E2:E73 and the list of countries with cities is F2:BY289. I think I can get around the problem if there's a way to automatically open a dropdown list once a macro button is clicked - do you know how this could be done?

 

[Yard]

And are those lists on the same worksheet as your dropdowns??

Can you please answer the question I've asked twice now? When you change the value of a cell in the worksheet with the dropdowns, do you get the message box telling you "Something has changed"?<!-- / message --><!-- sig --><!-- / message --><!-- sig -->

 

[anglais428]

Yes the lists are on the same sheet as the dropdowns
No message is given

 

[Yard]

Just to indulge me, can you right-click on the worksheet tab containing the dropdowns, click View Code and post what is contained in the code window which appears?

Thanks

 

[anglais428]

there's nothing there now. There was the code that you provided but I took that out.