Drop-down list with changeable values

[Liranzukz]

Hi,
I'm trying to create a drop down list with value changing in excel 2013.

Basically the goal is when I choose an option from the list, it changes it into a number.

The drop down list will be in a column that represents cities. I want to define numbers to the cities so when I go to a cell and choose a city from the list, it will change to the number I defined.

Is it possible?
Thanks in advance,
Liran.

 

[mumps]

Since you haven't mentioned in which columns your data is located, this macro assumes that your drop down list is in column A, the names of the cities are in column B and their corresponding numbers are in column C. The macro may have to be modified to suit your needs.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim foundCity As Range
    Set foundCity = Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundCity Is Nothing Then
        Target = foundCity.Offset(0, 1)
    End If
End Sub

 

[Liranzukz]

Since you haven't mentioned in which columns your data is located, this macro assumes that your drop down list is in column A, the names of the cities are in column B and their corresponding numbers are in column C. The macro may have to be modified to suit your needs.
Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Make a selection in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim foundCity As Range
    Set foundCity = Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundCity Is Nothing Then
        Target = foundCity.Offset(0, 1)
    End If
End Sub

Thank you!

 

[mumps]

You are very welcome.