Excel dropdown with name/value pairs

Question

I have workbook with 2 worksheets.

"Sheet2" has two columns:

|    A    |      B        | +---------+---------------+ |  code1  | description 1 | |  code2  | Descr 2       | 

Sheet1 has several columns, one of them (column D) is code. In this column i need a "drop box", what

• will show column Sheet2!B (the descriptions), and when the user selects one description
• will enter the code from the col:A.

It is possible to do without additional helper column in Sheet1? (Excel 2010)

So, need something what is dead simple in html:

<select>   <option value="code1">Description 1</option>   <option value="code2">Descr 2</option> </select> 

when when the user selects "Descr 2", the form get "code2".

This question probably is an duplicate - but i'm not sure - to: How to create dropdown with multiple columns in excel, but the only answer to it pointing me to an external site where the solution is for another problem.

Added a screenshot for more precise explanation:

Answer 1

Simple! Here is what we are going to get!

3 Steps Only:

1. Define a range to use as the lookup value

2. Create the dropdown list

3. Paste in some code

---

Step 1: Setup Sheet2 like this and define a Named Range as _descrLookup:

( Highlight -> Right-Click -> "Define Name..." ) 

This is an optional step, but it just makes it easy to follow for Step 3.

---

Step 2: In Sheet1, create the dropdown using Data Validation and use the VALUES YOU WANT TO BE SHOWN IN THE DROPDOWN as the source. In this example it's Sheet2 A2:A4 (see above image):

( Data -> Data Validation ) 

---

Step 3: Add some VBA code to Sheet1:

( Right-Click the tab Sheet1 -> View Code ) 

Paste this into the code window for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)     selectedVal = Target.Value      If Target.Column = 4 Then         selectedNum = Application.VLookup(selectedVal, Worksheets("Sheet2").Range("_descrLookup"), 2, False)          If Not IsError(selectedNum) Then             Target.Value = selectedNum         End If      End If End Sub