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: ``` ``` 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:

Accepted Answer

Simple! Here is what we are going to get!

3 Steps Only:

Define a range to use as the lookup value

Create the dropdown list

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
```

Excel dropdown with name/value pairs

Solution

Problem