Drop Down Lookup Return Value From Another Cell

  • Thread starter Thread starter Stephen Moeller
  • Start date Start date
S

Stephen Moeller

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
 
Not supported in Data validation dropdowns.

Using something else? Elaborate? What type of control, where is it located,
how is it populated?
 
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
Click to expand...



--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed)
 
Back
Top