Excel Drop Down List Automatically Changes Info(multiple) to Code

[nkendri24]

I want the ability to create a list using data validation and give it a define name. With this I want a drop down menu that will display a title but once selected give a code. Here is the code I have right now:

<code>
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 1 Then
    If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Target.Value = Worksheets("DataEntry").Range("A1") _
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("DataEntry").Range("Site_Name_ID"), 0), 0)
End If
exitHandler:
    Application.EnableEvents = True
    Exit Sub
End Sub

<code>

use this code to target columns 1, 2, 3, 4
all on worksheet DataEntry 
ranges A1, D1, G1, and J1
Range titles: Site_Name_ID, Grant_Code_ID, Area_od_Info_ID, and Source_Name_ID</code></code>

 

[Kenneth Hobson]

Since this forum does not allow file attachments, you have to explain in more detail as to what you want.

I don't know what "once selected give a code" means.

What list are you creating for the Data Validation to pick from?

Is your code creating the Data Validation? If so, in what cells?

The last part talks about target columns A to D but your Range titles are A1, D1, G1, and J1.

It looks like you want to create a Data Validation list for the Selection worksheet event and then use Change worksheet event for something else.

Maybe explain in steps an example data input and then what you expect to happen.

 

[nkendri24]

I would like to have two worksheets; one dataTemplate where the data validation will be, they will be in columns 1-4. they will be pulling information from a worksheet named DataEntry. In DataEntry the tables will be displayed as two columns; ID# (A1) and table name in (B1). there will be 4 tables. the 4 table names: Site_Name_ID(B1), Grant_Code_ID(E1), Area_of_Info_ID(H1), and Source_Name_ID(k1) (the ID# will be one column to the left of each of the columns listed). I would like to have the data validation create a list by pulling the info from table one (Site_Name_ID(B1)) and show the site Name data in the drop down but once selected display the ID# associated with the selection. The code in the first post does this function but I would like to do the same macro for the next three columns without having to repeat the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Cells.Count > 1 Then GoTo exitHandler
 If Target.Column = 1 Then                           <= location where data validation will be in dataTemplate worksheet
    If Target.Value = "" Then GoTo exitHandler
    Application.EnableEvents = False
    Target.Value = Worksheets("DataEntry").Range("A1") _ <= "DataEntry" is the worksheet and "A1" is the location of the table there 
        .Offset(Application.WorksheetFunction _
        .Match(Target.Value, Worksheets("DataEntry").Range("Site_Name_ID"), 0), 0) <= "Site_Name_ID" is the name of the table given by define name in the formula tab
 End If
 exitHandler:
    Application.EnableEvents = True
    Exit Sub
 End Sub