Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

 Hoe maak je een vervolgkeuzelijst, maar toon je verschillende waarden in Excel?

In het Excel-werkblad kunnen we snel een vervolgkeuzelijst maken met de functie Gegevensvalidatie, maar heb je ooit geprobeerd een andere waarde weer te geven wanneer je op de vervolgkeuzelijst klikt? Ik heb bijvoorbeeld de volgende twee kolomgegevens in kolom A en kolom B, nu moet ik een vervolgkeuzelijst maken met de waarden in de kolom Naam, maar als ik de naam selecteer in de gemaakte vervolgkeuzelijst, moet ik de bijbehorende waarde in de kolom Getal wordt weergegeven als in het volgende screenshot. Dit artikel introduceert de details om deze taak op te lossen.

doc dropdown verschillende waarden 1

Maak een vervolgkeuzelijst maar toon een andere waarde in de vervolgkeuzelijstcel


Maak een vervolgkeuzelijst maar toon een andere waarde in de vervolgkeuzelijstcel

Om deze taak te voltooien, voert u de volgende stap voor stap uit:

1. Maak een bereiknaam voor de celwaarden die u wilt gebruiken in de vervolgkeuzelijst, in dit voorbeeld voer ik de naam in de vervolgkeuzelijst in Naam Boxen druk vervolgens op Enter key, zie screenshot:

doc dropdown verschillende waarden 2

2. Selecteer vervolgens de cellen waarin u de vervolgkeuzelijst wilt invoegen en klik op Data > Data Validation > Data Validation, zie screenshot:

doc dropdown verschillende waarden 3

3. In de Data Validation dialoogvenster onder het Instellingen tabblad, kies Lijst van de Allow vervolgkeuzelijst en klik vervolgens op doc dropdown verschillende waarden 5om de lijst met namen te selecteren die u wilt gebruiken als vervolgkeuzelijsten in het bron tekstvak. Zie screenshot:

doc dropdown verschillende waarden 4

4. Na het invoegen van de vervolgkeuzelijst, klikt u met de rechtermuisknop op de actieve bladtab en selecteert u Bekijk code vanuit het contextmenu en in het geopende Microsoft Visual Basic voor applicaties venster, kopieer en plak de volgende code in de lege module:

VBA-code: geef een andere waarde weer uit de vervolgkeuzelijst:

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 verschillende waarden 6

Opmerking:: In de bovenstaande code, het nummer 5 binnen If Target.Column = 5 Then script is het kolomnummer dat in uw vervolgkeuzelijst staat, de "laten vallen" in deze selectedNum = Application.VLookup (selectedNa, ActiveSheet.Range ("dropdown"), 2, False) code is de bereiknaam die u in stap 1 heeft aangemaakt. U kunt deze wijzigen naar uw gewenste.

5. Sla deze code vervolgens op en sluit deze, nu, wanneer u een item uit de vervolgkeuzelijst selecteert, wordt een relatief andere waarde weergegeven in dezelfde cel, zie screenshot:

doc dropdown verschillende waarden 7


Demo: maak een vervolgkeuzelijst maar toon verschillende waarden in Excel

Kutools for Excel: met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen. Download en probeer nu gratis!

De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
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.
    DDVA · 1 months ago
    Is there a way to return multiple selections to the same field? I.E. I want to show the numbers for Tedi, Dave and Lucy on a single field seperated by a comma.
    Many thanks.
  • To post as a guest, your comment is unpublished.
    david · 1 months ago
    This works for me but is there a way to allow multiple selections in the same cell? I.e. I wanted to return the numbers for Tedi, Lucy and Dave in a single field seperated by a comma?
  • To post as a guest, your comment is unpublished.
    Muhammd Qadeer · 1 months ago
    Can this be done on different sheets? I mean, on sheet1 the range and on sheet2 the dropdown. How do I have to code this? Thanks.   
    • To post as a guest, your comment is unpublished.
      skyyang · 1 months ago
      Hello, Muhammd,
      To apply the drop down list in different worksheet, the following code may help you:
      Private Sub Worksheet_Change(ByVal Target As Range)
      'Updateby Extendoffice
      Dim xRg As Range
      selectedNa = Target.Value
      If Target.Column = 5 Then
      Set xRg = ActiveWorkbook.Names("DropDown").RefersToRange
      selectedNum = Application.VLookup(selectedNa, xRg, 2, False)
      If Not IsError(selectedNum) Then
      Target.Value = selectedNum
      End If
      End If
      End Sub


      Please try, thank you!
  • To post as a guest, your comment is unpublished.
    Cablegi · 6 months ago
    Good morning i hope someone can help me.
    I used the VBA code above and it worked perfectly giving me my drop down showing me Different Values In Drop Down List Cell.
    My problem is that I need a second drop down list showing different values in a second list cell, on the same sheet, can anyone help me in doing this Kind Regards Rene
  • To post as a guest, your comment is unpublished.
    CarlosHS · 9 months ago
    Se pueden usar diferentes “dropdown” en la misma hoja, para diferentes columnas y con diferentes rangos?
    Me explico, tengo una hoja en la que los usuarios introducen diferentes datos. En dos columnas 5 y 12 necesito validar la entrada con respecto a dos rangos diferentes de datos. Lo he probado solo con una columna y funciona perfectamente, pero no encuentro la manera de modificar el código VBA para la segunda columna

  • To post as a guest, your comment is unpublished.
    Winnie · 9 months ago
    What if I want to do more than one dropdown that returns different values on the same workshee and all happen to be in the same column one after another? Can you show me an example of the coding for two or more?
    • To post as a guest, your comment is unpublished.
      Jure · 7 months ago
      I need the same thing! I need two or three sets of values that are then inserted info a formula. One set of values is e.g. Green =1, blue = 2, red = 3 etc. And the other set of values is let's say square = 4, triangle = 3, circle = 0, etc. This code helped making one such dropdown, but I can't figure out how to make one more on the same sheet.
  • To post as a guest, your comment is unpublished.
    Marko · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      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!
  • To post as a guest, your comment is unpublished.
    THRASHER · 1 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Rod Cathcart · 1 years ago
    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
    • To post as a guest, your comment is unpublished.
      Joy · 1 years ago
      where exactly did you add this codes?
  • To post as a guest, your comment is unpublished.
    chaitanyagoud.r@gmail.com · 1 years 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 · 2 years 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 · 2 years 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 years ago
    Someone nkows how to search the value from right to left
  • To post as a guest, your comment is unpublished.
    Alondra · 2 years 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 years ago
    cómo buscar un valor hacia la izquierda
  • To post as a guest, your comment is unpublished.
    John J · 2 years 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 · 2 years 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.
      Mariel · 2 years ago
      Did you find a solution?
  • To post as a guest, your comment is unpublished.
    Mike · 2 years ago
    Anyone know how to get this to work in google sheets?
  • To post as a guest, your comment is unpublished.
    Marcus · 2 years 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 · 2 years 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 · 2 years 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 · 4 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 · 4 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 · 2 years ago
      Change here bro!
      selectedNum = Application.VLookup(selectedNa, Worksheets("YourSheetName").Range("dropdown"), 2, False)
      • To post as a guest, your comment is unpublished.
        clopez@sofpromed.com · 2 years 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 · 4 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 · 4 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 · 2 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.
        Karolis · 2 years ago
        Hi, help, it does'nt work, can you paste here all code for 2 colums?
      • To post as a guest, your comment is unpublished.
        Pawirodikromo Fiona · 2 years ago
        Just tried it. And it worked!! Thank you.
  • To post as a guest, your comment is unpublished.
    Tina · 4 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.