woensdag 18 mei 2016

Excel: validatie op basis van een benoemde kolom binnen een Excel Tabel

Er zijn in Excel een aantal manieren op een cel te valideren met een lijst. Ik loop ze hier langs.

A. Met een gewoon bereik:


Probleem: als er waarden worden toegevoegd, breidt het gekozen bereik zich niet automatisch uit.

B. Met een formule

Op de plek van Bron zetten we bijvoorbeeld:

=VERSCHUIVING(lijsten!$C$2;0;0;AANTALARG(lijsten!$C:$C)-1;1)

Dit heeft als voordeel dat het gekozen bereik zich automatisch uitbreidt. Komt er een element bij, dan wordt deze ook in de lijst getoond. Nadeel is dat de formule enigszins bewerkelijk is.

C. Met een naam

Op de plek van Bron zetten we dan bijvoorbeeld:

=Lijst_met_functie_Index

Een dergelijke naam verwijst dan naar een formule. In dit geval hebben we een aantal mogelijkheden:

=VERSCHUIVING(lijsten!$C$2;0;0;AANTALARG(lijsten!$C:$C)-1;1)

of bijvoorbeeld:

=INDEX(lijsten!$C:$C;2;1):INDEX(lijsten!$C:$C;AANTALARG(lijsten!$C:$C);1)

Namen met zulke formules hebben als voordeel dat het gekozen bereik zich automatisch uitbreidt. Komt er een element bij, dan wordt deze ook in de lijst getoond. Nadeel is dat de formules enigszins bewerkelijk zijn.

D: Met een naam binnen een tabel
We maken dan van een reeks eerst een tabel:


De naam van de tabel passen we vervolgens aan: 


Binnen deze tabel selecteren we de kolom A2:A7 en geven die een naam:


Deze naam ziet er dan enigszins eigenaardig uit:

=LijstMetEenTabel[[#Alles];[Lijst met een tabel]]

Het laatste stuk verwijst naar de kolomnaam. Als we dan bij gegevensvalidatie de bron aanpassen in:

=Lijst_met_een_tabel

Dan  verwijst deze naar een dynamisch bereik. Komen er waarden bij, dan breidt het bereik zich uit.

Dergelijke namen gebaseerd op een kolom binnen een tabel, kunnen we ook binnen een grafiek gebruiken. Deze beweegt dan ook mee met de tabel.

E. Met de functie INDIRECT verwijzen naar een kolom binnen een tabel

Bij Bron vullen we dan in:

=INDIRECT("LijstMetEenTabel[Lijst met een tabel]")


Voor verder Excel tips klik hier.







Een reactie posten