Soms kom je een oplossing tegen die zo mooi en zo simpel is om te doen, dat je die graag wilt bewaren voor het geval je het eens nodig denkt te hebben. En daarvoor heb ik deze blog opgezet. Daarom hoort deze hier zeker vermeld te worden.
Dit is een oplossing van Hasaan Fazal van de website LearnExcelToExcel. Ik kwam een het filmpje tegen op YouTube, maar op zijn eigen website is het niet te vinden. Daarom hieronder een vertaling naar het Nederlands.
Een voorbeeld: Je hebt een koffierooster waarop vermeld wordt wie van de medewerkers welke dag koffie voor de rest moet halen.
Dan wil je dat elke medewerker één keer gekozen kan worden en de ene niet per ongeluk 2x en en een ander niet.
Dit is een simpel voorbeeld maar het geeft goed weer hoe je een overzicht kunt maken waarin alleen nog die medewerkers gekozen kunnen worden die nog niet eerder gekozen zijn.
Hieronder een afbeelding van de twee tabellen en een berekening die je hier voor nodig bent.
In de linker tabel zie je rood omlijnd de kolom die gevuld moet worden met de medewerkernamen waarbij elke medewerker slechts één keer gekozen mag worden. Deze tabel wordt "Roostertabel" genoemd.
In kolom F staat een tabel met alle medewerkernamen en de tabel wordt "Medewerketabel" genoemd.
In kolom H staat de berekening die uitrekent welke namen wel in de medewerkertabel staan maar nog niet genoemd zijn in de roostertabel.
De medewerkertabel en de berekening kunnen op een ander werkblad staan maar voor het overzicht staan ze nu alle drie op één werkblad.
Met de functie AANTAL.ALS() kun je berekenen welke van de namen in de Medewerkertabel in de Wie-kolom staan vermeld. Als je in cel H5 de formule =AANTAL.ALS(Roostertabel[Wie];Medewerkertabel)
plaatst, dan zal het een 0 geven als een naam uit de Medewerkertabel nog niet in de Wie-kolom staat en een 1 als de naam wel in de Wie-kolom staat.
Met de Filter-functie kun je nu alle namen weergeven waarvan de Aantal.als-functie een 0 als antwoord geeft. De formule in H5 wordt dan:
=FILTER(Medewerkertabel;AANTAL.ALS(Roostertabel[Wie];Medewerkertabel)=0)
Hiermee wordt de lijst met beschikbare namen dus korter als er in de Wie-kolom namen worden vermeld die in de Medewerkertabel staan vermeld.
Om de namen in een dropdownlijstje alfabetisch gesorteerd te krijgen kan aan deze formule de SORTEREN()-functie worden toegevoegd. Er hoeft alleen alfabetisch gesorteerd te worden. De formule wordt dan:
=SORTEREN(FILTER(Medewerkertabel;AANTAL.ALS(Roostertabel[Wie];Medewerkertabel)=0);;1)
Om te voorkomen dat er namen in de Wie-kolom worden vermeld die niet in de Medewerkertabel staan, kan gebruik gemaakt worden van de gegevensvalidatie waarmee een keuzelijst/dropdownlijst gemaakt kan worden. Selecteer de cellen in de Wie-kolom en kies in het Gegevens-lint in het gedeelte "Hulpmiddelen voor gegevens" de optie Gegevensvalidatie. Op tabblad Instellingen moet in het veld "Toestaan" gekozen worden voor "Lijst" en in het veld "Bron" moet de eerste cel van de formule vermeld worden waarvan het rijnummer absoluut moet worden gemaakt en gevolgd door een hekje. in bovenstaande voorbeeld is dit dus: =H$5#.
In onderstaand voorbeeld is te zien dat voor de maandag Erik al is ingevuld. In de kolom Beschikbaar wordt door de filterformule deze naam niet meer weergegeven. Als daarna op de keuzelijst bij Dinsdag wordt geklikt is te zien dat deze naam niet meer in het lijstje wordt weergegeven.
Er is één nadeel aan deze keuzelijst. De naam moet altijd uit de keuzelijst geselecteerd worden en kan niet handmatig worden ingetypt. De gegevensvalidatie geeft dan namelijke en foutmelding. Waarschijnlijk moet Microsoft hiervoor de mogelijkheden nog een keer aanpassen. Maar mocht iemand een betere oplossing weten, dan hoor ik dat graag via het
contactformulier.
