Data Validation

[retired bill]

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

 

[Gord Dibben]

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

 

[retired bill]

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]

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

 

[retired bill]

Heelo again Gord and thanks for you answer. Sorry to get back late, and hope
you can still help with this. What I hope to get is - - - - After clicking in
column K, cell x, and then seeing the drop down list (which now shows up) and
clicking on a name, I want K7 to get the code letters from INFO1 column B.
Thanks again......Bill

 

[Gord Dibben]

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