Auto-update selected value in a dropdown list after updating the source

[jondu]

Hello,
I'm looking a way to auto-update a selected value in a dropdown list after updating the source.

Example : the source list :
A1 : China
A2 : Australia
A3 : Russia

In B1 : a dropdown list with the selected value Australia.

In A2, I finally change Australia by South Africa
=> the selected value in B1 must become South Africa.

I guess it's using combobox ?
I don't know anything in VBA code.

Thank you !

 

[jondu]

I must be also able to add other names in the source list.
Example :
A4 : United States

 

[jondu]

And I will have differents sheets :

Sheet1 = Containing Source List1 + dropdown cell 1
Sheet2 = Containing Source List2 + dropdown cell 2
Sheet3 = Containing Source List3 + dropdown cell 3
etc.

The linked sources lists and the dropdown menu lists are always in the same sheet (in other words : Source List1 in sheet1 and dropdown cell1 in sheet 2 is not planned).

 

[Akuini]

Try this:
- Create 1 named range: "Location", refers to: =Sheet1!$A$1:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A), 1)
- In cell B1, create a data validation, source: =OFFSET($A$1,0,0,COUNTA($A:$A),1)
- Copy the code below to the sheet code module:

Private deLoc As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.CountLarge <> 1 Then Exit Sub
    If Not Intersect(Target, Range("Location")) Is Nothing Then
        deLoc = Target.Value
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo skip:

If Target.Cells.CountLarge <> 1 Then Exit Sub
        If Not Intersect(Target, Range("Location")) Is Nothing Then
        Application.EnableEvents = False
            If UCase(Range("B1")) = UCase(deLoc) Then Range("B1") = Target
        Application.EnableEvents = True
        End If

Exit Sub
skip:
MsgBox "Error number " & Err.Number & " : " & Err.Description
Application.EnableEvents = True
End Sub

The workbook:

Dropbox

www.dropbox.com

 

[jondu]

ok great ! Thank you !
I have to develop a little bit more my workbook. It's possible that I will have some more questions, but later.
I keep you posted if needed !