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

I was able to turn on the Developer tab in Excel 2016 (15.33) for Mac OS X using the following steps:

1. In the menu select Excel->Preferences
2. In the Authoring section, click View
3. At the bottom of the dialog, check the Developer tab checkbox

4. The Developer tab is now visible in Excel

Answer 2

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

Answer 3

It sounds like Data Validation (allow List) combined with VLOOKUP will do what you want.

On sheet 2 set up your description/code list. Make it a named range (helps avoid circular reference problems).

On Sheet 1, in the description column, use Data Validation to make dropdown lists that reference the description column of the list. In the code column use the VLOOKUP function, keying off of the dropdown list value.

=IF(B4="", "", VLOOKUP(B4, FruitList, 2, FALSE))

Updated -

I'm starting to see what you mean by "without a helper column", but I'm not sure you can get exactly what you want. A fact of Excel design: what you see is what you get, i.e. the value that displays in the cell is the effective value of that cell. You can't have a cell display one value but "contain" another value. Such a thing is "dead simple" in HTML, but an HTML control isn't built for the same purpose as a cell in a spreadsheet. It's two things at the same time: a value, and a user interface presentation of that value. A spreadsheet cell can contain a way to determine a value (dropdown list, formula, etc.) but whatever value it reaches is going to be the value it shows.

Excel has forms support with things like combo boxes but I believe the value is still output to another cell.

The usual approach to this is to use data validation to create a dropdown list and have a separate column using VLOOKUP for the code. If you really can't have another column to contain the code then I'm not sure what to tell you. It would depend on how the data is to be consumed; are you trying to get a printout, or is the sheet being processed by another program?

Update 2

If you're really bent on not using a separate code column you may be able to use a combo box technique as partially described here:

http://www.contextures.com/xlDataVal10.html

It would be complicated. What you would have to do is (a) get the combo box to show up when the user selects one of the cells in column D, and (b) dynamically adjust the box's display items. It would involve VBA code and I'm not 100% sure it's possible. It certainly doesn't seem worth the effort.