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, 2022 in Others by Kithuzzz
• 38,010 points
1,765 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, 2022 by narikkadan
• 63,420 points

Related Questions In Others

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, 2022 in Others by narikkadan
• 63,420 points
867 views
0 votes
1 answer

Excel Power Query import (same file but with different month name)

Use the name manager function in Excel ...READ MORE

answered Dec 13, 2022 in Others by narikkadan
• 63,420 points
637 views
0 votes
1 answer

Name for excel graph problem with the vba generator

Change the name of the workbook : Sub Macro_name_graph2() ...READ MORE

answered Jan 7, 2023 in Others by narikkadan
• 63,420 points
464 views
0 votes
1 answer

Excel If Statement with Wildcards and Value Search

Try this: IFERROR(IF(IFERROR(LEFT(B2,2)*1,"")<=10,"",IFERROR(LE ...READ MORE

answered Apr 2, 2023 in Others by Kithuzzz
• 38,010 points
318 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,740 points
876 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,090 points
813 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,090 points
4,063 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
3,789 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered Feb 6, 2023 in Others by narikkadan
• 63,420 points
559 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, 2022 in Others by narikkadan
• 63,420 points
1,159 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