Data Validation & multiple columns

[Guest]

Please forgive me, but I inadvertently posted the message below in the
General Questions section and meant to post it here in the Programming
section.
************************************************************
In a Data Validation dropdown, is it possible to display more than one column
of data, but only "store" the value from the first column? For example,

Column A = Social Security Number
Column B = Last Name
Column C = First Name
Column D contains the Data Validation dropdown

When a user clicks on the Data Validation dropdown arrow, can the dropdown
box display the Social Security Number, Last Name, and First Name, but once a
person is selected, only "store" the Social Security Number in the cell?

Thanks in advance for any help.
Bob

 

[Guest]

Try:
in D1 insert this : =A1&" "&B1&" "&C1 - copy down

in E1 insert ur datavalidate list (sourse =column D) maby hide this column

in ur sheet code-module put this code :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E1")) Is Nothing Then Exit Sub
Range("E1") = Left(Range("E1"), 4) ' change 4 to lengh of ur code
End Sub



"Bob" skrev:

 

[Guest]

Thanks for your help. Unfortunately, there are two problems:

1) After selecting a person from the dropdown list, Excel appears to lock up
until I press the Esc key. Then I get a "Code execution has been
interrupted" error message.

2) The range where I need to have the dropdown boxes appear is E6:E20. Can
I simply replace "E1" with "E6:E20" in your code?

Thanks again,
Bob

 

[Guest]

y try this

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



"Bob" skrev:

 

[Guest]

Thanks. Unfortunately, the problem I described below still exists, except
now, after I have selected a name from the dropdown list, the list stays
open! As before, Excel appears to lock up until I press the Esc key, and
then I get a "Code execution has been interrupted" error message.
Bob

 

[Guest]

ok try my file

http://pmexcelent.dk/bob.xls



"Bob" skrev:

 

[Guest]

I tried your file, but after I select an item from the dropdown list, the
list remains open, the Status bar says "Calculating Cells: 100%", and
everything freezes (until I press the Esc key). Help!