Geselecteerde items verbergen uit keuzelijst in Excel
Wanneer je veel met Excel werkt, weet je ongetwijfeld dat het heel handig kan zijn om met keuzelijsten te werken. Een keuzelijst kan helpen om gegevens eenduidig en consistent in te voeren. De keuzelijst biedt echter nog meer mogelijkheden!
Met behulp van een keuzelijst kun je eenvoudig een schema maken, waarbij geselecteerde items niet meermaals mogen worden gekozen. Op deze manier kunnen activiteiten over verschillende dagen worden gepland, of kunnen werknemers worden ingedeeld.
In deze blog laten wij zien hoe je in Excel een keuzelijst kunt maken, waarin reeds geselecteerde items worden verborgen. Om deze lijst te maken hebben wij wél een aantal wat lastigere functies nodig. Dit keer dus geen beginners-blog…
Onze tip: wil je graag een ‘normale’ keuzelijst maken in Excel? Lees dan onze blog Keuzelijst in Excel. In deze blog leggen wij stap voor stap uit hoe je een keuzelijst kunt toevoegen in Excel!
Ons voorbeeld
In deze blog gaan wij aan de slag met onderstaand voorbeeld. In een jaar tijd willen wij twaalf steden bezoeken. Iedere maand kunnen wij één stad bezoeken en elke stad kan dus slechts één keer worden bezocht.
Concreet willen wij een keuzelijst bouwen, waarbij een stad niet twee keer kan worden gekozen. Wanneer wij invullen dat wij in januari naar Arnhem gaan, moeten wij Arnhem in februari niet meer kunnen kiezen.
Functie 1: AANTAL.ALS
De eerste functie die wij hiervoor nodig hebben, is de functie AANTAL.ALS. Omdat iedere stad maar één keer mag worden bezocht, moeten wij tellen hoe vaak een stad is ingevuld. Met de functie AANTAL.ALS tellen wij hoe vaak een stad wordt bezocht.
Allereerst typen wij de functie AANTAL.ALS in de functiebalk. Het bereik is het gebied, waarin Excel moet tellen hoe vaak Amsterdam is vermeld. In dit geval is dat G3 tot en met G14 (de kolom onder Bestemming, naast de maanden van het jaar). Het bereik hebben wij in deze formule absoluut gemaakt met behulp van F4. Het criterium is de stad die moet worden geteld. In deze formule is dat Amsterdam in cel A3.
De formule trekken wij vervolgens door naar onderen. Wanneer wij een van de steden invullen onder Bestemming, zien wij dat het Aantal verandert van 0 naar 1.
Functie 2: FILTER
De tweede functie die wij nodig hebben, is helaas iets ingewikkelder. Dit is namelijk de overloopfunctie FILTER. Deze functie vormt straks de basis voor ons keuzemenu.
Allereerst vullen wij in de functie FILTER het bereik in van de gegevens die moeten worden gefilterd. Dat is in dit geval de lijst met steden (A3:A14). Vervolgens vullen wij in welke gegevens getoond moeten worden als uitkomst van de filter. Wij willen alleen de steden zien die nog niet zijn ingevuld, dus waarbij het Aantal 0 is. Daarom vullen wij als tweede argument in: B3:B14=0.
In onderstaand scherm is geen enkele stad ingevuld. Onder Aantal staat voor elke stad dus een 0 en alle steden worden getoond in de filter.
Wanneer wij een aantal steden invullen, zie je dat de filter verandert. Dit is de basis voor onze keuzelijst.
Keuzelijst maken
Wanneer de filterfunctie goed werkt, kunnen wij een keuzelijst gaan maken. De keuzelijst plaatsen wij in cel G3, achter de eerste maand.
Klik op het tabblad Gegevens op de knop Gegevensvalidatie. Je vindt deze knop in het groepsvak Hulpmiddelen voor gegevens.
Het volgende dialoogvenster verschijnt:
Onder Toestaan kies je voor Lijst. Onder Bron verwijzen wij naar de filterfunctie die wij in cel C3 hebben ingevuld. Let op: wanneer je verwijst naar een filterfunctie moet je altijd een # achter de verwijzing plaatsen! Door een # te plaatsen weet Excel dat het een overloopfunctie betreft. Druk op OK om de keuzelijst te plaatsen.
De keuzelijst is nu achter de eerste maand geplaatst! Zoals je gewend bent, kun je de keuzelijst kopiëren naar de overige maanden.
Wanneer wij nu steden invullen, zien wij dat er steeds minder keuzemogelijkheden verschijnen.
Functie 4: ALS.FOUT
Tot slot moet er nog één ding gebeuren om de keuzelijst hélemaal goed te maken. Op het moment dat alle steden zijn ingepland, geeft de filterfunctie een foutmelding. Deze foutmelding kun je ook kiezen in de keuzelijst als alle plaatsen zijn ingevuld.
Om dit op te lossen voegen wij aan de formule FILTER in cel G3 de functie ALS.FOUT toe. Door deze functie toe te voegen weet Excel hoe het met fouten moet omgaan. Voor de functie FILTER plaatsen wij ALS.FOUT(. De functie die volgt, blijft helemaal hetzelfde. Achter de functie plaatsen wij ; en typen wij wat Excel moet tonen als er geen resultaat is. In dit geval willen wij dat er niets wordt getoond en typen wij “”. De formule sluiten wij af met een haakje. Er verschijnt nu geen foutmelding meer.
Indien gewenst, kun je natuurlijk de gegevens aan de linkerkant van het blad verbergen, of verplaatsen naar een ander tabblad.
Er is zó ontzettend veel mogelijk in Excel. In dit voorbeeld hebben wij een keuzelijst voor eigen gebruik gemaakt. Maar natuurlijk is het ook mogelijk om een soortgelijke lijst te ontwikkelen om door anderen te laten invullen! Wil je meer van dit soort handige tips & tricks? Volg dan onze cursus Excel – Gevorderd. Na deze cursus kent Excel geen geheimen meer voor jou!