Zo werkt Excel

Een benoemd bereik in Excel is een adres (bijvoorbeeld B3:B6) dat je een naam hebt gegeven. Het voordeel hiervan is dat je in formules in plaats van het adres de naam kunt intypen. Ook kun je het adres dat bij de naam hoort veranderen. Excel zorgt er dan voor dat in alle formules waar je de naam gebruikt, het adres wordt aangepast.

In eerdere artikelen (Een dynamische vervolgkeuzelijst mbv validatie en Invoer valideren met behulp van een vervolgkeuzelijst) heb ik voor het vullen van vervolgkeuzelijsten gebruik gemaakt van een benoemd bereik. Echter in deze voorbeelden was het bereik statisch. Als ik een nieuw element aan de lijst toevoeg komt deze niet automatisch in de vervolgkeuzelijst te staan. Om dat te bereiken hebben we een dynamisch benoemd bereik nodig.

In het bovenstaande voorbeeld heb ik het bereik B3:B6 de naam ‘Grootboekrekening’ gegeven. In cel D3 heb ik een validatie gemaakt die refereert naar dit bereik. Het zou nu mooi zijn als ik een nieuwe grootboekrekening toevoeg, deze ook automatisch is te zien in de vervolgkeuzelijst.

Laten we eerst eens kijken naar de definitie van het bereik Grootboekrekening. Dit kan door in het menu ‘Invoegen -> Naam -> Definiëren’ te kiezen.

Grootboekrekening verwijst nu naar de cellen B3:B6 op Blad2. Gelukkig staat Excel het ons toe om in het invoervak onder ‘Verwijst naar: ‘ in plaats van een celadres een formule in te voeren. Om het benoemde bereik ‘Grootboekrekening’ dynamisch te maken heb ik de volgende formulie ingevoerd:

=VERSCHUIVING(Blad2!$B$2;1;0;AANTALARG(Blad2!$B:$B)-1;1)

Door achtereenvolgens op [Toevoegen] en [OK] te klikken kom je weer terug in het werkblad. Als ik het lijstje met grootboekrekeningen uitbreid met rekening 9500 ‘Bijzondere Baten’, dan staat dit automatisch ook in de vervolgkeuzelijst.

In de formule maak ik gebruik van twee functies: VERSCHUIVING (Engels: OFFSET) en AANTALARG (Engels: COUNTA). VERSCHUIVING kent de volgende structuur: VERSCHUIVING(verw; rijen; kolommen; [hoogte]; [breedte]). Verw kun je zien als een anker. In het voorbeeld is dit cel B2. Vanaf dit punt kunnen we gaan schuiven. De omschrijving ‘Grootboekrekening’ hoeft niet in de vervolgkeuzelijst te komen. Daarom schuiven we 1 rij omlaag. De rest van de lijst staat wel in kolom B dus we schuiven 0 kolommen naar rechts. We zijn dan in cel B3. Vervolgens kunnen we de hoogte en breedte instellen. De breedte is duidelijk, 1 kolom. De hoogte is afhankelijk van het aantal elementen in de lijst. Daar hebben we de functie AANTALARG voor nodig.

De structuur van AANTALARG is (waarde1; [waarde2];…). In het voorbeeld heb ik de hele kolom B opgegeven. AANTALARG telt dan het aantal niet lege cellen in deze kolom. Van dit getal trek ik 1 af omdat cel B2 niet meegetelt hoeft te worden.

Vrij vertaal betekent de formule:

=VERSCHUIVING(Blad2!$B$2;1;0;AANTALARG(Blad2!$B:$B)-1;1)

Begin in cel B2. Ga 1 rij naar beneden en 0 kolommen naar rechts. Het bereik is vervolgens zo hoog als het aantal niet lege cellen in kolom B - 1 en de breedte is 1. Dit bereik komt precies overeen met de waarden voor de vervolgkeuzelijst.

In dit artikel heb ik een dynamisch benoemd bereik gebruikt voor een vervolgkeuzelijst. Dezelfde techniek kan ook heel goed worden toegepast op de zoek-matrix in een VERT.ZOEKEN functie, op de brongegevens voor een grafiek, of op de tabel die de basis is voor een draaitabel.

Mocht je het voorbeeld willen bekijken dan kun je het Excelbestand hier downloaden. Heb je nog een vraag of opmerking over dit artikel, voel je dan vrij om hieronder een comment achter te laten.