Data Validation using multiple columns
 

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

Data Validation using multiple columns
 

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'

Bob wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Data Validation using multiple columns
 

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" wrote:

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'

Bob wrote:
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
Contextures
http://www.contextures.com/tiptech.html

Data Validation using multiple columns
 

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

Bob wrote:
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" wrote:


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'

Bob wrote:

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
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Data Validation using multiple columns
 

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


"Debra Dalgleish" wrote:

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

Bob wrote:
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" wrote:


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'

Bob wrote:

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
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html