Problem with linked list

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Quick VBA solution:

Right click the sheet tab, click View Code, paste this into the code window:
Code:
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.
 

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Ok, want to give me a clue what "not working" means? ;)
 

anglais428

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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

Well-known Member
Joined
Nov 23, 2009
Messages
634
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
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:
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,556
Messages
5,596,820
Members
414,104
Latest member
imamalidadashzada

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top