Drop down list with corresponding value within the same cell.

[GravityDead]

Hello guys!
Need some quick help here. I want to make a drop down list which shows data from "Column A" when selecting but enter the corresponding value from "Column B" when selected. I hope I made myself clear

For example, this is "sheet2"
Column A - Column B
1947 - 500
1948 - 1000
1949 - 2000
1950 - 1500

I want to make a drop down in another sheet, say "sheet1", in cell K2 and all the cells down below.
When I click on K2, the drop down list should show the options, "1947", "1948", "1949", "1950" BUT when I select any of these values from Columns A in K2, the K2 cell should be filled with corresponding value from Column B.

Is it possible to do so or I HAVE to do it indirectly?

Regards.

 

[GravityDead]

Hey mumps, I finally got it to work with currency symbol too :D
I just added this line to the code after some googling

Target.NumberFormat = ChrW$(&H20B9&) & "##,##0"

 

[GravityDead]

Hello again mumps. thank you for your help so far man :D

Would it be possible to add a similar VBA to another column in the same sheet (first sheet)?
but this time data validation source will be from another sheet!

 

[GravityDead]

Another update, I even managed to do merge two Private Sub Worksheet_Change VBAs in one sheet after some more googling :D

Here is the final code that I'm using for future references

Private Sub Worksheet_Change(ByVal Target As Range)
My_Sub_A Target
My_Sub_B Target
End Sub
Public Sub My_Sub_A(ByVal Target As Range)
If Intersect(Target, Range("K:L")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim val As Range
Target.NumberFormat = ChrW$(&H20B9&) & "##,##0"
Set val = Sheets("AdvanceNotes").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
If Not val Is Nothing Then
Target = val.Offset(0, 1)
End If
End Sub
Public Sub My_Sub_B(ByVal Target As Range)
If Intersect(Target, Range("J:J")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim val As Range
Target.NumberFormat = ChrW$(&H20B9&) & "##,##0"
Set val = Sheets("CreditTracking").Range("B:B").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
If Not val Is Nothing Then
Target = val.Offset(0, 1)
End If
End Sub

mumps FTW :D

regards
GravityDead

UPDATE for moderators: forgive me for multiple replies, I didn't notice that I had the option of editing a post.

 

[mumps]

Glad it all worked out.