Excel Dynamische Selectielijst (dropdownmenu)

In deze aflevering bespreken we hoe je op een heel eenvoudige manier een dropdownmenu kan maken.

Tip: Bekijk de video en oefen samen met mij in het Excel bestand

Het werkbestand vindt u HIER

Als voorbeeld gebruiken we een tabblad waarin adresgegevens staan.

We gaan in een ander tabblad een selectielijst of dropdown menu maken die dynamisch gaat werken.

Wat is het verschil tussen een statische en dynamische dropdown menu?

Een statische dropdown menu geeft een aantal geselecteerde gegevens weer.

Maar zal bij nieuwe ingaven dit niet meer tonen als het buiten het bereik is.

Als je bv. in kolom B vanaf rij 4 tot rij 41 selecteert zal deze dropdown menu 37 selecties weergegeven.

Maar als we een nieuwe ingave in de adressen gaan invoeren op rij 42 zal de statische dropdown menu de gegevens van rij 42 niet tonen.

Bij een dynamisch zal dit wel worden getoond.

Voor een dynamische dropdown menu te maken hebben we 2 belangrijke formules nodig.

Dit zijn de verschuiving en aantalarg formules.

Deze formules zijn in het engels gekend als de offset(verschuiving) en counta(aantalarg).

Wat moeten we weten om deze 2 formules te begrijpen :

De waarde naar welke cel of beginpunt we willen verwijzen. We maken deze waarde ook absoluut met de $ tekens zodat deze waarde niet kan wijzigen in de kolom als ook in de rij. In ons voorbeeld is dit = verschuiving(adressenlijst!$B$3;
In deze waarde gaan we bepalen hoeveel rijen we naar beneden zullen gaan en zichtbaar zijn in de toepassing waar we de formule gaan gebruiken. In het voorbeeld gaan we 1 rij naar beneden en word dit dus de waarde, wat dus betekent dat we naar B4 kijken. =verschuiving(adressenlijst!$B$3;1;
In deze waarde gaan we bepalen hoeveel kolommen we gaan opschuiven. We willen in kolommen B blijven, dus onze waarde wordt 0. =verschuiving(adressenlijst!$B$3;1;0;
De volgende stap gaat de hoogte bepalen en hiermee gaan we straks de magie van het dynamische creëren door de aantalarg formule te gebruiken. Om even te oefenen kan je hier verschillende cijfers in gegeven. Als we bijvoorbeeld 40 cellen willen weergeven typen we 40 als waarde in. =verschuiving(adressenlijst!$B$3;1;0;40;

Wat moeten we nu doen om dit dynamisch te maken?

Hiervoor gaan we de =aantalarg formule gebruiken.

Deze formule telt hoeveel niet lege cellen er in het bereik zijn.

Ken je deze Functie nog niet?

Bekijk de video HIER

Als we bijvoorbeeld =aantalarg($C$4:$C500) intikt dan zal je in dit voorbeeld zien dat je er 42 zal krijgen.

Dit komt omdat er 42 cellen niet leeg zijn.

Tip : Het is steeds wel aangeraden dat uw eindbereik groot genoeg is en je een duidelijke markering en communicatie maakt, zodat de gebruiker steeds weet waar het bereik van de dynamische formule stopt.

In onze oefening zal je zien dat we met een tabel indeling werken.

Dit heeft soms wel enkele voordelen.

Als we vanaf het begin tot einde van de tabel gaan selecteren zal je zien dat de aantalarg formule, zich zal aanpassen naargelang de rij langer of korter wordt.

Tip: Gebruik Ctrl+shift+pijl naar beneden om je ingevulde bereik van boven naar beneden te selecteren.

In onze oefening plaatsen we de =aantalarg formule in cel H3 van onze dynamisch tabblad.

We gaan nu naar een willekeurige cel selecteren om onze verschuiving en aantalarg formule samen te brengen naar onze dynamische formule.

Dit wordt dus =verschuiving(adressenlijst!$B$3;1;0;$H$3;1)

Zoals je kan zien staat er na $H$3$ een 1. Deze waarde staat voor het aantal kolommen dat moet gebruikt worden. Hoe gaan we dit alles nu in een dynamisch dropdown menu verwerken?

Hiervoor zijn 2 manieren mogelijk :

kopieer de volledige formule .
Ga via het lint naar GegevensGegevensvalidatieGegevensvalidatieInstellingen Lijst en plak de volledige formule in de bron en druk op OK of enter.

Tip: Gebruik sneltoetsen ➡ Alt ➡e1➡h➡v

Als alles goed gaat werkt nu jouw dynamisch dropdown menu!Kopieer de volledige formule.
Ga via het lint naar FormulesNamen beherenNieuw...typ een willekeurige naam. Bij het vakje Naam en plak de formule bij : Verwijst naar: Wij gebruiken in dit voorbeeld Drop_naam
Ga via het lint naar GegevensGegevensvalidatieGegevensvalidatieInstellingen Lijst en typ of plak de willekeurige naam die je hebt gekozen.

Als alles goed gaat werkt nu jouw Dynamisch dropdown menu!

Veel succes en blijf zeker oefenen.

Vond je deze video interessant?

Wilt u soms een kleine donatie doen?

Alvast mijn dank.

Doe donatie