Bekijk video - Een afhankelijke vervolgkeuzelijst maken in Excel
Een Excel-vervolgkeuzelijst is een handige functie wanneer u formulieren voor gegevensinvoer of Excel-dashboards maakt.
Het toont een lijst met items als een vervolgkeuzelijst in een cel en de gebruiker kan een selectie maken uit de vervolgkeuzelijst. Dit kan handig zijn als u een lijst met namen, producten of regio's hebt die u vaak in een reeks cellen moet invoeren.
Hieronder ziet u een voorbeeld van een vervolgkeuzelijst in Excel:
In het bovenstaande voorbeeld heb ik de items in A2:A6 gebruikt om een vervolgkeuzelijst in C3 te maken.
Lezen: Hier is een gedetailleerde handleiding voor het maken van een Excel-vervolgkeuzelijst.
Soms wilt u echter meer dan één vervolgkeuzelijst in Excel gebruiken, zodat de items die beschikbaar zijn in een tweede vervolgkeuzelijst afhankelijk zijn van de selectie die in de eerste vervolgkeuzelijst is gemaakt.
Dit worden in Excel afhankelijke vervolgkeuzelijsten genoemd.
Hieronder ziet u een voorbeeld van wat ik bedoel met een afhankelijke vervolgkeuzelijst in Excel:
Je kunt zien dat de opties in Drop Down 2 afhankelijk zijn van de selectie die in Drop Down 1 is gemaakt. Als ik 'Fruits' selecteer in Drop Down 1, krijg ik de fruitnamen te zien, maar als ik Groenten selecteer in Drop Down 1, dan Ik krijg de groentenamen te zien in Drop Down 2.
Dit wordt in Excel een voorwaardelijke of afhankelijke vervolgkeuzelijst genoemd.
Een afhankelijke vervolgkeuzelijst maken in Excel
Hier zijn de stappen om een afhankelijke vervolgkeuzelijst in Excel te maken:
- Selecteer de cel waar u de eerste (hoofd) vervolgkeuzelijst wilt.
- Ga naar Gegevens -> Gegevensvalidatie. Dit opent het dialoogvenster voor gegevensvalidatie.
- Selecteer Lijst in het dialoogvenster voor gegevensvalidatie op het tabblad Instellingen.
- Geef in het veld Bron het bereik op dat de items bevat die in de eerste vervolgkeuzelijst moeten worden weergegeven.
- Klik OK. Hiermee wordt de vervolgkeuzelijst 1 gemaakt.
- Selecteer de volledige dataset (A1:B6 in dit voorbeeld).
- Ga naar Formules -> Gedefinieerde namen -> Maken vanuit selectie (of je kunt de sneltoets Control + Shift + F3) gebruiken.
- Vink in het dialoogvenster 'Benoemd uit selectie maken' de optie Bovenste rij aan en schakel alle andere uit. Hierdoor ontstaan er 2 namenreeksen (‘Fruits’ en ‘Groenten’). Vruchten genoemd assortiment verwijst naar alle soorten fruit in de lijst en Groenten genoemd assortiment verwijst naar alle groenten in de lijst.
- Klik OK.
- Selecteer de cel waar u de Afhankelijke/Voorwaardelijke Vervolgkeuzelijst wilt hebben (E3 in dit voorbeeld).
- Ga naar Gegevens -> Gegevensvalidatie.
- Zorg ervoor dat Lijst in is geselecteerd in het dialoogvenster Gegevensvalidatie op het tabblad Instellingen.
- Voer in het veld Bron de formule =INDIRECT(D3) in. Hier is D3 de cel die de hoofdvervolgkeuzelijst bevat.
- Klik OK.
Wanneer u nu de selectie maakt in Vervolgkeuzelijst 1, worden de opties in Vervolgkeuzelijst 2 automatisch bijgewerkt.
Download het voorbeeldbestand
Hoe werkt dit? - De voorwaardelijke vervolgkeuzelijst (in cel E3) verwijst naar =INDIRECT(D3). Dit betekent dat wanneer u 'Fruit' selecteert in cel D3, de vervolgkeuzelijst in E3 verwijst naar het genoemde bereik 'Fruit' (via de INDIRECT-functie) en dus alle items in die categorie vermeldt.
Belangrijke notitie: Als de hoofdcategorie uit meer dan één woord bestaat (bijvoorbeeld 'Seizoensfruit' in plaats van 'Fruit'), moet u de formule =INDIRECT(SUBSTITUTE(D3," ","_")) gebruiken in plaats van de eenvoudige INDIRECT-functie hierboven weergegeven.
- De reden hiervoor is dat Excel geen spaties in benoemde bereiken toestaat. Dus wanneer u een benoemd bereik maakt met meer dan één woord, voegt Excel automatisch een onderstrepingsteken in tussen woorden. Wanneer u bijvoorbeeld een benoemd bereik maakt met 'Seizoensfruit', krijgt het in de backend de naam Seizoen_Fruit. Het gebruik van de SUBSTITUTE-functie binnen de INDIRECT-functie zorgt ervoor dat spaties zijn omgezet in onderstrepingstekens.
Inhoud van afhankelijke vervolgkeuzelijst automatisch resetten/wissen
Als u de selectie hebt gemaakt en vervolgens de bovenliggende vervolgkeuzelijst wijzigt, zou de afhankelijke vervolgkeuzelijst niet veranderen en zou daarom een verkeerde invoer zijn.
Als u bijvoorbeeld de 'Fruit' als categorie selecteert en vervolgens Apple als het item selecteert, en dan teruggaat en de categorie wijzigt in 'Groenten', blijft de afhankelijke vervolgkeuzelijst Apple als het item weergeven.
U kunt VBA gebruiken om ervoor te zorgen dat de inhoud van de afhankelijke vervolgkeuzelijst opnieuw wordt ingesteld wanneer de hoofdvervolgkeuzelijst wordt gewijzigd.
Hier is de VBA-code om de inhoud van een afhankelijke vervolgkeuzelijst te wissen:
Private Sub Worksheet_Change (ByVal Target As Range) On Error Hervat Volgende If Target.Column = 4 Then If Target.Validation.Type = 3 Then Application.EnableEvents = False Target.Offset (0, 1).ClearContents End If End If exitHandler: Application.EnableEvents = True Exit Sub End Sub
De eer voor deze code gaat naar deze tutorial van Debra over het wissen van afhankelijke vervolgkeuzelijsten in Excel wanneer de selectie wordt gewijzigd.
Hier leest u hoe u deze code kunt laten werken:
- Kopieer de VBA-code.
- In de Excel-werkmap waar u de afhankelijke vervolgkeuzelijst hebt, gaat u naar het tabblad Ontwikkelaar en klikt u in de groep 'Code' op Visual Basic (u kunt ook de sneltoets gebruiken - ALT + F11).
- In het VB Editor-venster, aan de linkerkant in de projectverkenner, ziet u alle werkbladnamen. Dubbelklik op degene met de vervolgkeuzelijst.
- Plak de code in het codevenster aan de rechterkant.
- Sluit de VB-editor.
Wanneer u nu de hoofdvervolgkeuzelijst wijzigt, wordt de VBA-code geactiveerd en wordt de inhoud van de afhankelijke vervolgkeuzelijst gewist (zoals hieronder weergegeven).
Als je geen fan bent van VBA, kun je ook een eenvoudige voorwaardelijke opmaaktruc gebruiken die de cel markeert wanneer er een mismatch is. Dit kan u helpen om de mismatch visueel te zien en te corrigeren (zoals hieronder weergegeven).
Hier zijn de stappen om mismatches in de afhankelijke vervolgkeuzelijsten te markeren:
- Selecteer de cel met de afhankelijke vervolgkeuzelijst(en).
- Ga naar Home -> Conditionele opmaak -> Nieuwe regel.
- Selecteer in het dialoogvenster Nieuwe opmaakregel 'Een formule gebruiken om te bepalen welke cellen moeten worden opgemaakt'.
- Voer in het formuleveld de volgende formule in: =ISERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)),1,0))
- Stel het formaat in.
- Klik OK.
De formule gebruikt de functie VERT.ZOEKEN om te controleren of het item in de afhankelijke vervolgkeuzelijst het item uit de hoofdcategorie is of niet. Als dit niet het geval is, retourneert de formule een fout. Dit wordt gebruikt door de ISERROR-functie om TRUE te retourneren, wat voorwaardelijke opmaak vertelt om de cel te markeren.
Misschien vind je de volgende Excel-zelfstudies ook leuk:
- Gegevens extraheren op basis van een keuze uit een vervolgkeuzelijst.
- Een vervolgkeuzelijst maken met zoeksuggesties.
- Selecteer meerdere items in een vervolgkeuzelijst.
- Maak meerdere vervolgkeuzelijsten zonder herhaling.
- Bespaar tijd met formulieren voor gegevensinvoer in Excel.
