Note: The other languages of the website are Google-translated. Back to English
English English▼
X
en Englishar Arabichy Armenianzh-CN Chinese (Simplified)zh-TW Chinese (Traditional)cs Czechda Danishnl Dutchfr Frenchde Germanel Greekhu Hungarianid Indonesianga Irishit Italianja Japaneseko Koreanpl Polishpt Portuguesero Romanianru Russiansl Slovenianes Spanishsv Swedishth Thaitr Turkishuk Ukrainianvi Vietnamesecy Welsh

 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

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
    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 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

 

The Best Office Productivity Tools

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. 60-day money back guarantee.
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

 

Comments (42)
No ratings yet. Be the first to rate!
Tina
Tina
This comment was minimized by the moderator on the site
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.
Reply
0
0
Lee Ann Brennan
Lee Ann Brennan
This comment was minimized by the moderator on the site
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?
Reply
0
0
Tony
Tony     Lee Ann Brennan
This comment was minimized by the moderator on the site
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
Reply
0
0
Pawirodikromo Fiona
Pawirodikromo Fiona     Tony
This comment was minimized by the moderator on the site
Just tried it. And it worked!! Thank you.
Reply
0
0
Karolis
Karolis     Tony
This comment was minimized by the moderator on the site
Hi, help, it does'nt work, can you paste here all code for 2 colums?
Reply
0
0
Chi-Cheng Chien
Chi-Cheng Chien     Tony
This comment was minimized by the moderator on the site
Tony: Correct, but next step is to avoid encouraging: a) violations of the DRY Principle (and therefore increased: a.1) chances of errors and costs to fix them and a.2) costs of potential future changes / enhancements) by not encouraging "Copy-Paste" programming by showing how it can be "refactored" to reduce duplicate code, and b) hard-coded literal ("magic" "number" / "string") constants by declaring and using Named Constants that are much harder to mistype without triggering a compiler error. The only things different between the two copy-pasted code blocks are the column #s and the range names, so as much as possible, everything else should not be duplicated, so, instead, the code should, e.g., be:

' -- NOTE: Declare "EmptyString" in a global declarations Module.
Public Const EmptyString as String = ""

' -- Column #s. NOTE: In practice, e.g., "Col5Header" would be, e.g., "ProductID", and "Col9Header" would be, e.g., "SalesID".
Private Const Col5HeaderColumnNumber As Integer = 5
Private Const Col9HeaderColumnNumber As Integer = 9

' -- Columns' Drop-Down Lists' Range Names
Private Const Col5HeaderDropDownRangeName as String = "col5HeaderDropDownRangeName"
Private Const Col9HeaderDropDownRangeName as String = "col9HeaderDropDownRangeName"

Sub Worksheet_Change _
( _
ByVal Target As Range _
)

' -- BEGIN For applicable =columns, pull in ID for description selected from drop-down lists.
Dim dropDownListRangeName as String
dropDownListRangeName = EmptyString
Select Case Target.Column
Case Col5HeaderColumnNumber
dropDownListRangeName = Col5HeaderDropDownRangeName
Case Col9HeaderColumnNumber
dropDownListRangeName = Col9HeaderDropDownRangeName
End Select ' -- Case Target.Column
If (dropDownListRangeName <> EmptyString) Then
Dim selectedId as String
selectedId = Application.VLookup(selectedNa, ActiveSheet.Range(dropDownListRangeName), 2, False)
If Not IsError(selectedId) Then
Target.Value = selectedId
End If ' -- Not IsError(selectedId)
End If ' -- (dropDownListRangeName <> EmptyString)
-- END For applicable =columns, pull in ID for description selected from drop-down lists.

End Sub
Reply
Report
0
0
Mike K
Mike K
This comment was minimized by the moderator on the site
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?
Reply
0
0
Charmin
Charmin
This comment was minimized by the moderator on the site
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.
Reply
0
0
Phyo
Phyo     Charmin
This comment was minimized by the moderator on the site
Change here bro!
selectedNum = Application.VLookup(selectedNa, Worksheets("YourSheetName").Range("dropdown"), 2, False)
Reply
0
0
César López - Sofpromed
César López - Sofpromed     Phyo
This comment was minimized by the moderator on the site
"YourSheetName" makes reference to the sheet that contains the data range or the sheet where I want to use the dropdwon list?
Reply
Report
0
0
Charmin
Charmin
This comment was minimized by the moderator on the site
How does the formula work when you want to list the data on a separate sheet/tab in the workbook?
Reply
0
0
ty
ty
This comment was minimized by the moderator on the site
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.
Reply
0
0
William
William
This comment was minimized by the moderator on the site
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
Reply
0
0
Marcus
Marcus
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Mike
Mike
This comment was minimized by the moderator on the site
Anyone know how to get this to work in google sheets?
Reply
0
0
Rizwan Ahmad
Rizwan Ahmad
This comment was minimized by the moderator on the site
i want to select multiple option from dropdown list.
result like this: AA1001,BB1002
is it possible?
Reply
0
0
Mariel
Mariel     Rizwan Ahmad
This comment was minimized by the moderator on the site
Did you find a solution?
Reply
0
0
John J
John J
This comment was minimized by the moderator on the site
Si los datos de la lista están en otra hoja, cuál sería el código? Gracias.
Reply
0
0
Alondra
Alondra
This comment was minimized by the moderator on the site
cómo buscar un valor hacia la izquierda
Reply
0
0
Alondra
Alondra
This comment was minimized by the moderator on the site
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
Reply
0
0
Alondra
Alondra
This comment was minimized by the moderator on the site
Someone nkows how to search the value from right to left
Reply
0
0
Dre
Dre
This comment was minimized by the moderator on the site
I need to use the same dropdown in more than one column, what would be the code?
Reply
0
0
AC
AC
This comment was minimized by the moderator on the site
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?
Reply
0
0
Chaitanya Goud R
Chaitanya Goud R
This comment was minimized by the moderator on the site
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.
Reply
Report
0
0
Rod Cathcart
Rod Cathcart
This comment was minimized by the moderator on the site
This helped me immensely, thank you. Since my table was on a different sheet than my list box I added a couple lines of code to accomplish that and also to keep the screen from flashing.

Application.ScreenUpdating = False
Sheets("SheetWithTableOnIt").Activate

Sheets("SheetWithDropDownListOnIt").Activate
Application.ScreenUpdating = True
Reply
0
0
Joy
Joy     Rod Cathcart
This comment was minimized by the moderator on the site
where exactly did you add this codes?
Reply
0
0
THRASHER
THRASHER
This comment was minimized by the moderator on the site
I get a Compile error: Syntax error on the line "If Trarget.Column = 6 Then" when I try to use the code? Any idea as to why?
Reply
0
0
Marko
Marko
This comment was minimized by the moderator on the site
It is working but when you exit a file and open again it is not working...it can t be saved as .xls only as .xlsm is there any solution for that? Thanks
Reply
0
0
skyyang
skyyang     Marko
This comment was minimized by the moderator on the site
Hello, Marko,After copying and pasting the code in your workbook, when you save the file, you should save it as an Excel Macro-Enabled Workbook format, please try, thank you!
Reply
Report
0
0
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations