Drop-down list with changeable values

Liranzukz

New Member
Joined
Jan 16, 2018
Messages
5
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.
 

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.
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.
Code:
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
 
Upvote 0
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.
Code:
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!
 
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

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
Back
Top