Data Validation using multiple columns

[Guest]

In a Data Validation dropdown, I need to display more than one column of
data, but only "store" the value from the first column? For example:

Column A = ID Number (4 characters long)
Column B = Last Name
Column C = First Name
Column D = Column A & “ “ & Column B & “ “ & Column C
Column E = Data Validation where Source = $D1:$D20

When a user clicks on the Data Validation dropdown arrow, the dropdown would
display the ID Number, Last Name, and First Name. But once a person is
selected, only the ID Number would be "stored" in the cell.

“excelent†was kind enough to provide me with the following macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E6:E20")) Is Nothing Then Exit Sub
Target = Left(Target, 4)
End Sub

Unfortunately, after I select a name from the dropdown list, the list stays
open and Excel appears to lock up until I press the Esc key. Then I get a
"Code execution has been interrupted" error message.

Can anyone tell me how to modify the macro above so that it works properly?
Any help would be greatly appreciated.
Bob

 

[Debra Dalgleish]

There's a sample file here that you could adapt:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

 

[Guest]

Debra,
Thanks for the tip! Since I am a VBA novice, may I ask you two quick
questions?
The range of my "lookup" table is K4:L12. So would I change your line of
code from:

Target.Value = Worksheets("Codes").Range("A1")

to:

Target.Value = Worksheets("Codes").Range("K4:L12")?

Also, given my different lookup range, would the "0), 0)" in the line:

..Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)

remain the same?

Thanks again for all your help.
Regards,
Bob

 

[Debra Dalgleish]

The line:
Target.Value = Worksheets("Codes").Range("A1")
should change to refer to your starting cell:
Target.Value = Worksheets("Codes").Range("K4")

The line:
.Match(Target.Value, Worksheets("Codes").Range("ProdList"), 0), 0)
shouldn't have to change, assuming you have a sheet named codes, and a
range named ProdList, which is the range of cells with the combined
data, e.g. L4:L12

 

[Guest]

Debra,
Thanks for all your help! I sincerely appreciate it.
Regards,
Bob