Bill

Been away but here is some code to place into the worksheet module.

Bernie Dietrick originally gave me this for another project.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Vals As Range
Dim R As Range
Dim RR As Range

Set R = Range("K7:K56")
Set Vals = Sheets("INFO1").Range("A1:B50")

If Intersect(Target, R) Is Nothing Then Exit Sub

On Error GoTo endit
Application.EnableEvents = False

For Each RR In Intersect(Target, R) 'Only check the changed cells
RR.Value = Application.VLookup(RR.Value, Vals, 2, False)
Next RR
endit:
Application.EnableEvents = True
End Sub



Gord



On Wed, 22 Apr 2009 16:40:13 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Not sure what needs changing.

Do you want INFO1 column A to get the code letters from INFO1 column B?

Or do you want K7 to get the code letters from INFO1 column B


Gord


On Wed, 22 Apr 2009 16:16:13 -0700, retired bill
wrote:

Thank you Gord for you time. I shall try to give some more explaination in
hopes you can give me some help with this.
I have a columa in my worksheet that I want data in from the list. The
range of the column is K7 thru K56. I have another worksheet that I use data
validation from. Call it INFO 1 worksheet. I have names in column "A" of
this worksheet, and in the very next column "B" I have the code letters that
I WANT to appear when I click on the name from the drop down list. When I
now click on a cell in column K, i.e. k7, the drop down list appears with
my selections for column "A" from worksheet INFO1. how do I get it to
replace the name in column "A" with the code letters in column "B" on my main
worksheet...... Thanks fot your time and trouble, Bill

"Gord Dibben" wrote:

To return a reference code value to the DV dropdown cell itself will require
VBA.

Much easier to just use VLOOKUP in an adjacent cell, but event code could do
the trick in the DV dropdown cell.

If you want to go with the event code, post back with more details and some
cell ranges


Gord Dibben MS Excel MVP

On Wed, 22 Apr 2009 13:08:01 -0700, retired bill
wrote:

I have a column in my worksheet in which I am using data validation with
V-lookup from another worksheet. When I click on the drop down list in the
column it of course shows the listing from the other worksheet. In the next
column in the lookup worksheet I have a reference code for the item in the
first column. What I would like to do, is when someone clicks on the drop
down list in my MAIN worksheet, and selects a value, I would then like it
replaced with the reference code that corresponds with the value being
clicked on. If this does not make sense, please let me know and I will try
to explain it again in another way. Thanks