Auto correct/populate drop downs

[JToulson91]

Hi all,

First time poster - bare with me please!

I have a spreadsheet that is very dependent on selected location. I currently have data-validation dropdown lists for continent, country and city - the formula for each list is

=indirect('previous dropdown')

; ie the 'Continent' dropdown is in A1 the country data validation formula =Indirect(A1), referring to a named selection elsewhere in the workbook.

This all works brilliantly and updates the dropdowns each time an entry is changed - the list in the 'City' dropdown changes if you change which country is chosen.

However, when I have selected Europe > Paris > France, I can change the continent selection to Africa for example and the country and city dropdowns remain unchanged, implying that Paris is in Africa (this is made for sharing around the globe and has to be idiot-proof!); is there a way to make the second and third lists auto-update/refresh, so that if the continent is changed, the current country choice refreshes to the first option of the new continent list and the city option changes to the first in the list of that country?

I've read many posts and formus but nothing quite gets to what I'm after - I'd rather not use VBA but can if necessary!

Hope that's all clear!

TIA

 

[L. Howard]

Try something like this event macro in the sheet module.

Where A1 is the continent and B1 & C1 are the dependents.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
  Range("B1,C1").ClearContents
  Range("B1").Select
End Sub

Howard

 

[JToulson91]

Hi Howard,

That's great, thank you - it works brilliantly for changing the continent!

Is there a way to extend the functionality of this to cover a change in country too? Country and City now go blank if the Continent is changed, but Paris could still be in Germany if you change the Country!

I've attempted to use your code to add this in multiple ways, but my VBA knowledge is less than nothing, so it hasn't had any effect - see below.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("C10")) Is Nothing Then Exit Sub
  Range("D10,E10").ClearContents
  Range("D10").Select
  If Intersect(Target, Range("D10")) Is Nothing Then Exit Sub
  Range("E10").ClearContents
  Range("E10").Select


End Sub

(C10 = continent, D10 = Country and E10 = City)

TIA!

Jordan

 

[JToulson91]

An added note actually:

Would it be possible for it to auto-select the first option in the list rather than clear the contents? So if you select Africa, the Country auto-selects "Algieria" (first in my list of countries for Africa) and then "Algiers" for the City (first in my list of cities for Algeria).

And then if you changed the Country to Morocco, the City automatically changes to eg; Casablanca?

Thanks again for your help!

 

[L. Howard]

Maybe this will work for you.

Where:
1. Select a Continent
2. Country and the City is cleared
3. Blank Country cell is selected
4. Select a Country
5. Blank City cell is selected

I'm not sure if I can make the first Country and first City appear on the selection of a Continent.

How about posting a link to an example workbook so I can see the lists layouts etc. I use Drop Box but there are other utilities also.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C10")) Is Nothing Then
    Range("D10,E10").ClearContents
    Range("D10").Select
  ElseIf Not Intersect(Target, Range("D10")) Is Nothing Then
    Range("E10").Select
  End If
End Sub

'(C10 = continent, D10 = Country and E10 = City)

 

[JToulson91]

Hi Howard,

Thanks, I've tweaked it slightly to the following and that works brilliantly:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4")) Is Nothing Then
    Range("C4,D4").ClearContents
    Range("C4").Select
  ElseIf Not Intersect(Target, Range("C4")) Is Nothing Then
    Range("D4").ClearContents
    Range("D4").Select
  End If
End Sub

You can find an example workbook here: " https://www.dropbox.com/s/yf4mio foqotwkbt/example.xlsm?dl=0 " (minus the spaces). It's basic - meaning I haven't included all the continents/countries, but that's the form it currently has. The only macro is the one you've written above.

Thanks,

Jordan

 

[L. Howard]

I get an error message on that link, cannot download it.

Please try again.

I had success to a degree on a test that seems to work.
It looks something like this on my sheet. (Just for the first dependent only), where LetterList is a named range.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C10")) Is Nothing Then
    Range("D10").Formula = "=OFFSET(C10,-7,MATCH(C10,LetterList,0)+2)"
    Range("D10").Select
  ElseIf Not Intersect(Target, Range("D10")) Is Nothing Then
    Range("E10").Select
  End If
End Sub

 

[L. Howard]

Woops, forgot the spaces issue, I have the workbook.

Howard

 

[L. Howard]

Hi Jordan,

Here is my attempt at the worksheet you describe.

https://www.dropbox.com/s/0xclyrr2cjxoakc/Continent Country City Drop Down example.xlsm?dl=0

Three Continents with five Countries each and various numbers of cities.

I found it easier to arrange the data as you see it on sheet two.

Test fly, and see if it fits the bill.

Of course you can change the continents, countries and cities to suit. Should be fairly easy to add or subtract as you wish.

You should take note of the formulas in the code (which are also on the sheet for demo purposes) See hoe the rows/column increment to achieve the first country and first city.

There are two Cases in the code for a BLANK cell and a ONE SPACE cell. In the Continent drop down you can test by selection the two apparent blanks. MsgBox will tell you which is which. Toss out, just in there for a demo. Leave one Case Else.

Howard

 

[JToulson91]

Hi Howard,

That sounds great, thank you!

Hadn't received a notification of your reply, so I'll look at the workbook you've created tomorrow and let you know how I get on!

Thanks,

Jordan