Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

 How to create drop down list but show different values in Excel?

In Excel worksheet, we can quickly create a drop down list with the Data Validation feature, but, have you ever tried to show a different value when you click the drop down list? For example, I have the following two column data in Column A and Column B, now, I need to create a drop down list with the values in Name column, but, when I select the name from the created drop down list, the corresponding value in Number column is displayed as following screenshot shown. This article will introduce the details to solve this task.

doc dropdown different values 1

Create drop down list but show different value in drop down list cell

Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Create drop down list but show different value in drop down list cell

To finish this task, please do with the following step by step:

1. Create a range name for the cell values you want to use in the drop down list, in this example, I will enter the name dropdown in the Name Box, and then press Enter key, see screenshot:

doc dropdown different values 2

2. Then select cells where you want to insert the drop down list, and click Data > Data Validation > Data Validation, see screenshot:

doc dropdown different values 3

3. In the Data Validation dialog box, under the Settings tab, choose List from the Allow drop down, and then click doc dropdown different values 5 button to select the Name list which you want to use as drop down values in the Source text box. See screenshot:

doc dropdown different values 4

4. After inserting the drop down list, please right click active sheet tab, and select View Code from the context menu, and in the opened Microsoft Visual Basic for applications window, copy and paste the following code into the blank Module:

VBA code: Display different value from the drop down list:

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
    selectedNa = Target.Value
    If Target.Column = 5 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

doc dropdown different values 6

Note: In the above code, the number 5 within If Target.Column = 5 Then script is the column number that your drop down list located, , the “dropdown” in this selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False) code is the range name you have created in step 1. You can change them to your needed.

5. Then save and close this code, now, when you select an item from the drop down list, a relative different value is displayed in the same cell, see screenshot:

doc dropdown different values 7


Demo: Create drop down list but show different values in Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Chaitanya Goud R · 1 months ago
    Hi,
    The code was working fine if we are defining the list and creating the drop-down in the same sheet.
    But how can we achieve defining the list of values and codes in one sheet and the drop-down created in another sheet?
    This same code is not working as it is showing and error in this line ("selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)").
    Also, I have a requirement like, if I have multiple lists defined in one sheet with ID and Names and multiple drop-downs in another sheet where one drop-down value is dependent on selected value in another drop-down.

    Hope you understood my query.

    Please help me in resolving this issue.
  • To post as a guest, your comment is unpublished.
    AC · 1 months ago
    Hi!
    This is really useful! Thank you!
    I'm running in the situation where the cell does not update automatically or when using the refresh function. I have to click in another cell and then click back on the cell in work to get it to display the value.
    I am currently working with in Office Standard 2019. Does anyone know if this issue is related to the version on excel i'm using?
  • To post as a guest, your comment is unpublished.
    Dre · 1 months ago
    I need to use the same dropdown in more than one column, what would be the code?
  • To post as a guest, your comment is unpublished.
    Alondra · 2 months ago
    Someone nkows how to search the value from right to left
  • To post as a guest, your comment is unpublished.
    Alondra · 2 months ago
    Private Sub Worksheet_Change(ByVal Target As Range)
    selectedNa = Target.Value
    If Target.Column = 5 Then

    Sheets("Nombre de la hoja en donde esta la lista").Activate
    selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
    Sheets("Nombre de la hoja en donde estas trabajando").Activate
    If Not IsError(selectedNum) Then
    Target.Value = selectedNum
    End If
    End If
    End Sub
  • To post as a guest, your comment is unpublished.
    Alondra · 2 months ago
    cómo buscar un valor hacia la izquierda
  • To post as a guest, your comment is unpublished.
    John J · 2 months ago
    Si los datos de la lista están en otra hoja, cuál sería el código? Gracias.
  • To post as a guest, your comment is unpublished.
    Rizwan Ahmad · 3 months ago
    i want to select multiple option from dropdown list.
    result like this: AA1001,BB1002
    is it possible?
  • To post as a guest, your comment is unpublished.
    Mike · 6 months ago
    Anyone know how to get this to work in google sheets?
  • To post as a guest, your comment is unpublished.
    Marcus · 7 months ago
    How would the code Need to Change if I wanted to create a reference/link in E1 to the source of the Dropdown list based on the selected value?
    The Benefit would be that In case of a change in the dropdown source (e.g. "Henrik" => "Hendrik" the change would automatically get reflected in E1.
  • To post as a guest, your comment is unpublished.
    William · 7 months ago
    In this example, what if you want it to look at a value in each of the cells in 5, but put the value in the adjacent cell in 6
  • To post as a guest, your comment is unpublished.
    ty · 8 months ago
    this doesn't work in current versions of excel- outdated. Data validation then list no longer shows up in vba as an excel object have tried already multiple times and it doesn't show up.
  • To post as a guest, your comment is unpublished.
    Charmin · 2 years ago
    How does the formula work when you want to list the data on a separate sheet/tab in the workbook?
  • To post as a guest, your comment is unpublished.
    Charmin · 2 years ago
    How does the formula the work when you want to add the data on a separate sheet in the workbook? I want to hide the data.
    • To post as a guest, your comment is unpublished.
      Phyo · 1 years ago
      Change here bro!
      selectedNum = Application.VLookup(selectedNa, Worksheets("YourSheetName").Range("dropdown"), 2, False)
      • To post as a guest, your comment is unpublished.
        César López - Sofpromed · 3 months ago
        "YourSheetName" makes reference to the sheet that contains the data range or the sheet where I want to use the dropdwon list?
  • To post as a guest, your comment is unpublished.
    Mike K · 2 years ago
    Nothing more frustrating than typing in a detailed question only to have it blown away. If you type the wrong 6 digit code to verify your human, it wipes out the posted message. Might want to fix that.

    Now my comment is this: I tried to do the exact same thing you showed in the video and written instructions and all I get is when I select a name in the list is the name and not the number. Also, how is this even working since data validation should limit the choices to what's in the list only. How is this tricking the system?

    In the past I've always had to assign vba code to button or a shortcut, how is this code activated? How do you test to make sure it's working?
  • To post as a guest, your comment is unpublished.
    Lee Ann Brennan · 2 years ago
    What if I want to do more than one dropdown that returns different values on the same worksheet? Can you show me an example of the coding for two or more?
    • To post as a guest, your comment is unpublished.
      Tony · 1 years ago
      Lee Ann

      If you just copy and paste the code from the If to the EndIf and change the column # and Table it should work:


      Sub Worksheet_Change(ByVal Target As Range)
      selectedNa = Target.Value
      If Target.Column = 5 Then
      selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
      If Not IsError(selectedNum) Then
      Target.Value = selectedNum
      End If
      End If
      If Target.Column = 9 Then
      selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown1"), 2, False)
      If Not IsError(selectedNum) Then
      Target.Value = selectedNum
      End If
      End If
      End Sub

      I'm not saying this is the correct way but it worked on my test version. I'm using Excel 2013
  • To post as a guest, your comment is unpublished.
    Tina · 2 years ago
    Can this be done on different sheets? I mean, on sheet1 the dropdown and on sheet2 the range. How do I have to code this? Thanks in advance. Tina.