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]

Quick VBA solution:

Right click the sheet tab, click View Code, paste this into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
    Me.Range("A2").ClearContents
End If
End Sub

Press Alt+Q to return to Excel.
Test.

 

[anglais428]

It's not working I'm afraid.

 

[Yard]

Ok, want to give me a clue what "not working" means?

 

[anglais428]

Just nothing changes on the sheet.
How should it appear - it's not on the Macro list when i click on the icon?

 

[Yard]

The code sits in the code module for the worksheet itself. It won't appear in the macro list.

Did you right-click the worksheet tab, click View Code and paste the code into the code window which appears?

 

[anglais428]

Yea, I've done that and there's no change in the problem

 

[Yard]

OK, just to check:

Cell A1 contains the country.
Cell A2 contains the city.
When cell A1 is changed, you want cell A2 to be cleared.
You've pasted the code above into the code module for the worksheet containing these cells.
Macros are enabled.
You have entered a value in cell A1 and A2.
When you enter a new value in cell A1, cell A2 is not cleared?

 

[anglais428]

Firstly thanks for your help on this.
When both cells are blank, I select "Estonia" from the dropdown in cell A1, The dropdown in cell A2 automatically shows only cities that are in Estonia (linkage to A1). I select Tallinn from the dropdown in A2.
So I have Estonia in A1 and Tallinn in A2. When I click the dropdown in A1 and change Estonia to UK, cell A1 becomes UK but cell A2 still says Tallinn, even though the dropdown for A2 has changed to show only UK cities.
The code you gave has been pasted and macro's are enabled.

thanks

 

[Yard]

I'm a bit lost then.

That code tests if the cell which has been changed has the address "A1". If it does, then it clears the contents of cell "A2". I have it working in front of me now.

Change the code to this:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Something changed in range " & Target.Address(0, 0)
If Target.Address(0, 0) = "A1" Then
    Me.Range("A2").ClearContents
End If
End Sub

This will tell us whether the code is firing when the worksheet is changed.