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

 

[JToulson91]

That's absolutely brilliant, thank you Howard.

Your time and help is greatly appreciated!

Jordan

 

[L. Howard]

Great, good to hear, and you are welcome.

Howard

 

[L. Howard]

Hi Jordan,

A heads up on a small mistake in the formulas.

Delete the one in RED and use the one in BLUE. (Code below)

Both work, but the BLUE keeps the OFFSET reference cell and the row (-1) consistent with the other formulas.

So if you arrow down one row at a time in the YELLOW formulas (P3 to P5) on the sheet, the increment is NOT consistent.
Change the CASA formula on the sheet to the BLUE in the code, and then it will be consistent in both places, VBA Editor and the worksheet.

You will see no change to the results, in the drop downs.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("B4")) Is Nothing Then

Select Case Target.Value

Case Is = "Africa"
   
 [COLOR="#000080"] [C4].Formula = "=IF(B4=Sheet2!A2,OFFSET(Sheet2!A2,(-1),1),"""")"[/COLOR]
 [COLOR="#FF0000"] '[C4].Formula = "=IF(B4=Sheet2!A2,OFFSET(Sheet2!A3,(-2),1),"""")"[/COLOR]

  [D4].Formula = "=IF(C4<>Sheet2!B1,"""",OFFSET(Sheet2!B1,MATCH(C4,Africa,0),0))"
  [C5].Activate
  
Case Is = "Europe"
  [C4].Formula = "=IF(B4=Sheet2!A3,OFFSET(Sheet2!A3,(-2),7),"""")"
  [D4].Formula = "=IF(C4<>Sheet2!H1,"""",OFFSET(Sheet2!H1,MATCH(C4,Europe,0),0))"
  [C5].Activate
  
Case Is = "Australia"
  [C4].Formula = "=IF(B4=Sheet2!A4,OFFSET(Sheet2!A4,(-3),13),"""")"
  [D4].Formula = "=IF(C4<>Sheet2!N1,"""",OFFSET(Sheet2!N1,MATCH(C4,Australia,0),0))"
  [C5].Activate
  
Case Is = " "
   MsgBox "Blank with a single space"
   
Case Is = ""
   MsgBox "Blank with no space"
   
End Select

  End If

End Sub