Excel Afhankelijke keuzelijsten

Met gegevensvalidatie kunnen keuzelijsten aan cellen worden toegevoegd. Hier zal worden uitgelegd hoe de keuzemogelijkheden van een keuzelijst afhankelijk gemaakt kunnen worden van de gekozen waarde in een andere keuzelijst. Dit wordt ook wel getrapte validatie genoemd. In onderstaand voorbeeld zijn in cellen A1 en B1 keuzelijsten opgenomen. De bedoeling is dat als in A1 voor jongen gekozen wordt, dat dan alleen jongensnamen (cellen D1:D4) zichtbaar zijn in keuzelijst B1 en bij de keuze voor meisje alleen meisjesnamen (cellen E1:E4).

afhankelijke_keuzlijsten1_400x200

Om dat te realiseren moet eerst aan cel A1 met gegevensvalidatie een keuzelijst worden toegevoegd met keuzemogelijkheden jongen en meisje:

afhankelijke_keuzlijsten2_405x335

De volgende stap is dat de bereiken D1:D4 en E1:E4 een naam moet worden gegeven. De namen moeten zijn jongen en meisje. Dit kan het eenvoudigst gedaan worden door eerst het bereik te selecteren en vervolgens in het naamvak de betreffende naam te typen:

afhankelijke_keuzlijsten3_375x210

De laatste stap is het toevoegen van de afhankelijke keuzelijst aan cel B1. Als bron voor deze lijst moet ingegeven worden: =INDIRECT(A1)

afhankelijke_keuzlijsten4_405x335

Hierna zullen in de keuzelijst van B1 alleen maar meisjesnamen verschijnen als in A1 meisje gekozen wordt en jongensnamen als in A1 jongen gekozen wordt. De keuzelijst van B1 is hierdoor afhankelijk gemaakt van de keuzelijst van A1.

 

Interesse in andere tips & trucs? Ga dan naar ons tips&trucs-overzicht of neem een kijkje op onze pagina met tutorials.

Reacties op onze tutorials en tips&trucs zijn van harte welkom. Dat kan door hieronder een reactie toe te voegen of door rechtstreeks contact met ons op te nemen.

27 reacties op Excel afhankelijke keuzelijsten

  • Hoi

    deze 2 trapsvalidatie heb ik door,
    echter als ik een 3 trapvalidatie wil doen met bijvoorbeeld de waardes “klas” in kolom F
    Met de gegevensvalidatie in C1 ingesteld op list met =INDIRECT(B1) lukt dit niet , wat zou een oplossing zijn ?

    • De drietrapsvalidatie met INDIRECT werkt op dezelfde manier als de tweetrapsvalidatie. Van alle mogelijke keuzes van de 2e trap, moeten een benoemde bereiken worden gemaakt van de keuzemogelijkheden. Met INIDRECT(B1) kan dan deze keuzelijst ingesteld wordt. Als B1 leeg is bij het instellen van de keuzelijst van C1, volgt een foutmelding dat de bron resulteert in een fout. Dat klopt omdat dan geen naam en dus geen keuzelijst kan worden gevonden. Je kunt dan gewoon op toch doorgaan klikken, waarna drietrapsvalidatie goed zou moeten werken. Als B1 al gevuld is bij het instellen van keuzelijst in C1 dan volgt deze foutmelding niet (op voorwaarde dat de benoemde bereiken goed zijn geconfigureerd).

  • Hallo,

    Wanneer ik dit toepas werkt het prima voor keuzevelden met 1 woord. Echter heb ik ook een aantal keuzevelden met meerdere woorden, wanneer ik een spatie toepas bij het invoeren van het naamveld krijg ik een foutmelding. Hoe kan ik dit oplossen?

    • Dag Linda,
      Bedankt voor je reactie. Je kunt inderdaad geen spaties gebruiken bij een benoemd bereik. Een workaround hiervoor is om de spaties in de bereiknaam te vervangen door een underscore. De formule voor de de afhankelijke keuzelijst moet dan worden:

      =INDIRECT(SUBSTITUEREN(A1;" ";"_"))

      Met deze workaround is het mogelijk om opties te gebruiken die spaties bevatten.
      Groet, Manfred

      • Je hebt voor mij een uur frustratie opgelost, waarvoor dank!

      • Ik zou deze formule graag willen gebruiken, maar ik zie in de formule substitueren 4 argumenten, hierboven zie ik 6 punt-komma’s wat 7 argumenten zou moeten betekenen, [b]&quot[/] heb ik nog nooit gezien in een formule, kun je hier wat meer over uitleggen? Bij voorbaat dank.

        • Blijkbaar is in de tussentijd wat misgegaan met de weergave van dubbele aanhalingstekens, waardoor de formule er raar uitzag. Dat heb ik nu aangepast.

  • Geweldige tip, dank je wel!

  • Kan ik bovenstaande ook gebruiken voor het volgende. Ik heb een lijst met namen in kolom A en met beroepen in kolom B:

    Jan Timmerman
    Piet Slager
    Kees Bakker
    Frans Timmerman
    Hans Bakker

    Nu wil ik in een andere cel met gegevensvalidatie een lijst waarin alleen namen van een bepaald beroep voorkomen. Bijvoorbeeld timmerman. Deze lijst moet dan dynamisch zijn. Met andere woorden als ik een timmerman toevoeg moet deze naam worden opgenomen in de keuzelijst.

    • Als je deze techniek wilt gebruiken, dan zul je alle mensen met dezelfde functie onder elkaar moeten zetten. En dit bereik geef je dan de naam van de functie. Elke keer wanneer je iemand toevoegt zul je dit bereik dan moeten aanpassen. Deze techniek met INDIRECT werkt namelijk niet meer zogenaamde dynamische bereiken met behulp van de functie VERSCHUIVING. Een andere optie is om een hulpkolom te gebruiken waarin je met behulp van formules de juiste namen laadt op basis van een gekozen functie. Deze hulpkolom moet dan gekoppeld worden aan de keuzelijst.

  • Bedankt voor de tips.
    Ik snap alleen nog niet hoe ik de gegevensvalidatie nu in heel kolom B moet maken.
    Moet ik dan INDIRECT($B$) of iets dergelijks typen?

    Alvast bedankt.

    • Hallo Dagmar,
      Ik begrijp niet helemaal wat je wilt bewerkstelligen. Je wilt voor alle cellen in kolom B gegevensvalidatie instellen, waarbij de waarden die getoond worden afhankelijk zijn van de waarde van een andere cel in bijvoorbeeld kolom A? Of bedoel je wat anders?

  • Hallo,

    Nog een variatie op de 2-trapsvariatie. Ik wil graag 2 kolommen met verschillende keuzelijsten afhankelijk maken van één keuze in een eerdere kolom. Dit lukt niet omdat de ik de twee verschillende keuzelijsten niet dezelfde naam kan geven.

    Voorbeeld: de eerste keuze is: ontbijt, lunch of diner. Op basis van deze keuze (laten we zeggen diner) wil ik dat er in een andere kolom een keuzelijst verschijnt met daarin soort keuken (Frans, Italiaans, Oosters), maar ook nog een kolom met een keuzelijst hoofdingrediënt (Vlees, vis, groenten).

    Kan iemand mij hiermee helpen?

  • Hallo
    Is het ook mogelijk om zo’n lijst te maken waarbij er telkens een keuze minder wordt weergegeven. Bv: Als je in cel D1 ‘Jan’ hebt aangeklikt, je dan nog voor de volgende cellen de keuze krijgt tussen ‘Piet, Klaas en Tom’. En als je dan bv in D2 ‘Klaas’ kiest, je voor de volgende enkel nog de keuze hebt uit ‘Piet en Tom’. Alvast bedankt.

  • Hallo,
    als men in de eerste kolom een keuze maakt, is het mogelijk om de inhoud van de tweede kolom blanco te maken indien de waarde in kolom 1 veranderd?
    thanks…

    • Dat kan alleen met VBA. Bijvoorbeeld:

      Private Sub Worksheet_Change(ByVal Target As Range)
          If Target.Address = "$A$1" Then
              Range("B1").ClearContents
          End If
      End Sub
  • Hoi Manfred,

    Is het überhaupt mogelijk om formules te gebruiken in de naambereiken van de jongens/meisjes-namen?

    • Jazeker. Op deze pagina zie je daarvan een eenvoudig voorbeeld met de formule INDIRECT,

  • Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "A1:A10" Then
            Range("B1:B10").ClearContents
        End If
    End Sub

    Als ik deze code invoer, werkt het dan als volgt?: als A1 leeg wordt gemaakt wordt de waarde enkel in B1 ook gewist. Als A2 leeg wordt gemaakt wordt de waarde enkel in B2 ook gewist. Enzovoort.

    • Nee, dat werkt dan zo niet. De volgende code werkt dan wel:

      Private Sub Worksheet_Change(ByVal Target As Range)
          If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
              Target.Offset(, 1).ClearContents
          End If
      End Sub
      • Bedankt voor al je antwoorden!

  • Hallo,

    Ik vind je uitleg superduidelijk! Echter denk ik niet dat ik dit kan toepassen op mijn bestand. Ik heb namelijk een Excelbestand met 2 tabbladen. In tabblad 1 staan alle gegevens van de klant met bijhorende contractnummers. In tabblad 2 geef ik elke dag de gedane handelingen in en dit gebeurt per contractnummer. Bijvoorbeeld:

    Tabblad 1:
    klantnummer; naam v/d klant; contractnummer;
    123456; Pieter Claes; contr18468;
    123456; Pieter Claes; contr26483;
    123456; Pieter Claes; contr31245;
    123456; Pieter Claes; contr44897;
    789012; Joris Terwin; contr46853;
    789012: Joris Terwin; contr56489;

    Tabblad 2:
    Datum; klantnummer; contractnummer;
    10/04/2019; 123456; ????????

    Mijn vraag is nu: In tabblad 2 vul ik steeds de datum en het klantnummer in. Het contractnummer zou dan automatisch moeten aangevuld worden aan de hand van tabblad 1. Ik dacht dus aan afhankelijke keuzelijst via gegevensvalidatie maar ben de hoop ondertussen bijna kwijt…

    Kan je mij helpen hierbij?

    Dank je wel!

    • Je wilt dan op de plek van de vraagtekens een dropdown hebben waarin je een contractnummer kunt kiezen die bij die klant hoort?
      Dat kan inderdaad niet met de afhankelijke keuzelijsten zoals omschreven op deze pagina. Een mogelijke oplossing hiervoor is een hulpkolom met een kleine macro. Als je me een representatief voorbeeldbestand stuurt wil ik hier wel even vrijblijvend naar kijken.

      • Hoi Manfred

        Bedankt voor je supersnelle reactie. Hoe kan ik het bestandje doorsturen?

  • Ik heb redelijk onder de knie hoe ik een drop-down menu met subcategorieën moet aanmaken. Nou vind ik het prettig om dit in een ander tabblad te doen dan waar het menu in tevoorschijn moet komen. Op één of andere manier krijg ik dit niet voor elkaar. Heb je daar tips en trucs voor?

Geef een reactie

Het e-mailadres wordt niet gepubliceerd.