Data Validation Show Codes Only

[excelbytes]

I have the following data:



I want to create a drop down list with the items in column "C", but when an item is selected, I only want it to show the 2 letter code as shown in column "A". I found this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("A2:A30")) Is Nothing Then Exit Sub   'Addresses of the cells with the dropdown

    'Turn off events to keep out of loops
    Application.EnableEvents = False


     'Change the "C1:D10" in the next line to the address of your list
    Target.Value = Application.VLookup(Target.Value, Range("A2:B8"), 2, False)

    'Turn events back on to get ready for the next change
    Application.EnableEvents = True
End Sub

But I can't get it to work. The dropdown list is on a worksheet called 'Tools" and the codes and descriptions are on a worksheet called "Data".

Thanks,

 

[HighAndWilder]

VLOOKUP is working fine. What I need to happen is if there is a K in B15, then I need the list from P2:R75 to appear in H15. If at the same time, there is an H in B16, then I need the list from P2:R107 to appear in H16. What is happening is that, as long as there are only K's or H's in column B, then the correct list appears in column H. However, as soon as there is a K anywhere in column B, that satisfies the first part of the IF statement and puts the list from P2:P75 in all of column H, so when I click on the corresponding row where there is an H in column B, it won't show the correct list. It needs to look at each row independently to determine which list to put in each cell of column H depending on if there is a K or H in column B.

So you want a Multiple Column Drop down list then? P2:R107 and P2:R75.

One way is to have a column combining multiple columns and then extract the value that you want as you did before.

Another way is to have a Userform pop up when a cell is selected.