Excel dropdown with name value pairs

0 votes

I have a 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 "dropbox", which

  • 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 an additional helper column in Sheet1? (Excel 2010)

So, need something that is dead simple in HTML:

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

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

Added a screenshot for a more precise explanation: enter image description here

Oct 7 in Others by Kithuzzz
• 20,660 points
72 views

1 answer to this question.

0 votes

Solution

  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:

define a named range for the VLookup

( 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):

Set data validation to the source from Sheet 2

( 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
answered Oct 7 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
0 answers

Excel 2010, how to keyboard shortcut to filter dropdown checkboxes?

how do I get to the filter ...READ MORE

Feb 10 in Others by Edureka
• 13,640 points
40 views
0 votes
0 answers

Download Excel sheet from .NET Core 3.1 Web API with jQuery Ajax client

I am trying to download an Excel ...READ MORE

Feb 10 in Others by Edureka
• 13,640 points
370 views
0 votes
0 answers

Excel: Highlighting duplicates with exact matches

1 I've got two columns of data, and ...READ MORE

Feb 17 in Others by Edureka
• 13,640 points
55 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,720 points
449 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
395 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
3,366 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
2,822 views
0 votes
1 answer

How to merge two cells in excel with same field name

Insert 2 new columns, G & H. Enter ...READ MORE

answered Oct 7 in Others by narikkadan
• 37,660 points
55 views
0 votes
1 answer

Number format in excel: Showing % value without multiplying with 100

You just need to select Custom from ...READ MORE

answered Oct 21 in Others by narikkadan
• 37,660 points
28 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP