Dynamisch bereik in Excel maken
Dynamisch bereik met formules
In dit voorbeeld gaan we voor het gemak even uit van een klantenbestand. Hier kunnen we vanuit gaan dat hier regelmatig mutaties in plaatsvinden. Indien de zaken goed lopen zullen er regelmatig nieuwe klanten worden toegevoegd.
Via een vervolgkeuzelijst kun je de betreffende debiteur kiezen waarna alle overige gegevens worden ingevuld.
Vervolgkeuzelijst maken
Om een vervolgkeuzelijst te maken selecteer je de cel waarin je de lijst wilt weergeven (bijvoorbeeld B17). Ga in het lint naar het tabblad Gegevens de groep Hulpmiddelen voor gegevens en klikt op Gegevensvalidatie. Er opent zich een nieuw venster.
Onder de tab Instellingen kies je bij Toestaan: voor “Lijst” en bij Bron: voer je het bereik in (zie onderstaande afbeelding).
Iedere keer als je een klant aan het klantenbestand toevoegt dient het bereik van de vervolgkeuzelijst te worden aangepast. Dit is niet wenselijk. Het mooiste zou zijn als dit een dynamisch bereik zou zijn. Hieronder vind je de uitleg hoe we dit gaan doen.
Bereik dynamisch maken
In de formule maak ik gebruik van de functies: VERSCHUIVING en AANTALARG. De functie VERSCHUIVING zit als volgt in elkaar: VERSCHUIVING(verw; rijen; kolommen; [hoogte]; [breedte]).
Het argument verw kun je zien als een baken. Je geeft hier aan vanuit welke cel je de verschuiving wilt uitvoeren. In het voorbeeld is dit cel A3. Vanaf deze cel willen we gaan verschuiven
Rijen geeft aan hoeveel rijen je vanaf verw omhoog of omlaag wilt verschuiven. In het voorbeeld starten wij in cel A3. In deze cel staat de omschrijving van de betreffende kolom. Deze willen we niet in de vervolgkeuzelijst weergegeven hebben. We schuiven daarom één rij omlaag, dus vullen hier een 1 in. Vul je een negatief getal in dan verschuif je omhoog.
Kolommen geeft aan hoeveel kolommen je vanaf verw naar links of naar rechts wilt verschuiven. Omdat wij in dezelfde kolom blijven vullen wij hier een 0 in.
Met de laatste twee argumenten stellen we de hoogte en de breedte in van het dynamische bereik. Om de hoogte van het bereik te bepalen maken we gebruik van de functie AANTALARG. Deze functie telt het aantal niet-lege cellen.
De functie AANTALARG zit als volgt in elkaar: AANTALARG(waarde1; [waarde2;...]). Voor waarde1 vullen we kolom A:A in waarna alle niet lege cellen in deze kolom worden geteld. Hier trekken we vervolgens 2 vanaf omdat de waarden in cel A1 en A3 niet meetellen.
De uiteindelijke formule is dan als volgt: =VERSCHUIVING(klantenbestand!$A$3;1;0;AANTALARG(klantenbestand!$A:$A)-2;1).
Namen beheren
Bovenstaande formule kunnen we invoeren bij de bron van de vervolgkeuzelijst, maar we kunnen de formule ook opslaan in een makkelijker te onthouden naam. Dit doe je met de optie Naam definiëren.
Ga in het lint naar het tabblad Formules de groep Gedefinieerde namen en klik op Naam definiëren.
Er opent zich een nieuw venster genaamd Nieuwe naam.
Vul bij het invoerveld Naam: een makkelijk te onthouden naam voor je formule.
Bij het veld Verwijst naar: voer je de formule =VERSCHUIVING(klantenbestand!$A$3;1;0;AANTALARG(klantenbestand!$A:$A)-2;1) in.
Vervolgens klik je op OK en de gedefinieerde naam is opgeslagen.
Maak voorkeuzelijst dynamisch
Pas nu de verwijzing van de vervolgkeuzelijst aan door de verwijzing aan te passen naar =selKlantnaam. Klik op OK om de dynamische vervolgkeuzelijst op te slaan.
Ben je de naam vergeten van het benoemd bereik? Selecteer het invoerveld Bron: en druk op de functietoets F3. Er opent zich een nieuw venster met alle benoemde bereiken. Dit voorkomt ook de kans op typefouten.
Voeg je nu een nieuwe klant toe aan het klantenbestand, dan wordt deze automatisch toegevoegd aan de vervolgkeuzelijst.
