You might try something like this. In this case I pick values from one
column, look up values in the corresponding row in another column and put
the resultes into another column in the data sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
'for the worksheet into which you want the lookup results
Dim cELL_KEY, cELL_RESULT As Range 'These are defined names of columns
'containing the data
Dim i, j As Long
Application.EnableEvents = False 'turn off events so we don't loop when
'we change things.
Set cELL_KEY = Worksheets("units").Range("units_keys")
Set cELL_RESULT = Worksheets("units").Range("units_abbreviations")
i = Target.Row 'get the current cell row (content has just been changed)
j = Target.Column 'get the current cell column (content has just been
'changed)
'in this case, I used 'Data Validate' to pop up a LIST using =cELL_KEY as
'the validate code
'I only wanted to do this for column 14. It could be done for every other
'column or ... your choice.
If (j = 14) Then 'look up KEY and place KEY code into tARGET sheet
'in this case, I was doing lookup and putting a parameter in 14 and
'then looking up a value
'to put into column 7, in the same row. It could have gone back into
'14
Cells(i, 7).Value = Application.WorksheetFunction.Lookup _
(Target.Value, cELL_KEY, cELL_RESULT)
End If
Application.EnableEvents = True 'turn events back on.
End Sub
Greetings!
I would like to have a drop down pick list that returns the value from
an adjacent cell instead of the one picked. For example, I have a list
of Units of Measures in one column and their abbreviations in an
adjacent column (i.e. "hundred weight" in cell b2 and "CWT" in cell a2)
I would like to have the drop down list displaying the long
description in column B to pick from but return the abbreviation from
column A. Can not figure it out, what am I missing?
SM
--
bz
please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.
(e-mail address removed)
