In mijn vorige artikel (Kunst en Excel) heb ik 11 voordelen van het gebruik van tabellen in Excel besproken.

Op een forum voor Excel-experts heb ik gevraagd of er ook nadelen zijn aan tabellen.

Daar zijn 2 reacties op gekomen, waarvan de tweede aanleiding is geweest voor dit artikel.

Nadelen Tabellen in Excel

De eerste reactie ging over de naam, die de tabel automatisch krijgt (Tabel1, de volgende Tabel2 etcetera). De naam moet dan nog aangepast worden om zodoende een zinvolle beschrijving te krijgen; tsja, hier zie ik zo gauw geen echt probleem (en ook geen oplossing).
Maar ook werd aangegeven, dat wanneer de tabel uit de werkmap wordt verwijderd de naam nog wel actief zou blijven. Zover ik kan nagaan, geldt dat wel voor cellen (of celbereiken) die een naam krijgen en daarna worden verwijderd, maar niet voor tabellen!

In de tweede reactie werd aangegeven, dat tabellen niet bij een gegevensvalidatie kunnen worden gebruikt.
Ja, daar heeft Microsoft een steek laten vallen: standaard kan alleen een celbereik als keuzelijst worden opgegeven. Wel is het mogelijk om een ‘gewone’ naam als bron daarvoor op te geven, maar dan moet je in de tabel de gewenste reeks een aparte naam geven en er voor zorgen, dat bij uitbreiding van de lijst deze naam ook wordt aangepast.
Maar voor dit probleem hebben we gelukkig wel een oplossing!

Gegevensvalidatie

In het Voorbeeldbestand heb ik een tabblad Param opgenomen, waar 2 tabellen staan:

1. in het eerste blok staan afkortingen en omschrijvingen voor alle afdelingen die kunnen worden gebruikt.
   De tabel heeft de naam tblAfd gekregen (hoe je een tabel maakt en de naam kunt aanpassen is in het artikel Kunst en Excel besproken).
2. daarnaast staat een overzicht van de mogelijke kostensoorten; deze tabel heeft de naam tblKst

Deze twee tabellen gaan we gebruiken voor een gegevensvalidatie:

1. de invoer in cel B3 van het tabblad GegVal moet beperkt worden tot die drie afkortingen voor de afdelingen, die in Param zijn opgenomen (dus D, C en O).
2. kies de menuoptie Gegevens en dan binnen het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie:
   
3. in het vervolgscherm kiezen we bij Toestaan de optie Lijst.
4. dan komt de optie Bron tevoorschijn; klik op  en selecteer dan de cellen B3 tot en met B5 in het tabblad Param.
5. Klik OK. Aan cel B3 in GegVal is nu een keuzepijltje toegevoegd; als je daar op klikt kun je één van de drie mogelijkheden kiezen.

Op dezelfde manier kan ook de invoer in cel C3 beperkt worden tot de drie mogelijke kostensoorten uit Param (de cellen E3 t/m E5).

Door de cellen B3 en C3 te kopiëren kan de gegevensvalidatie uitgebreid worden naar andere cellen.

Een groot nadeel is, dat als er nieuwe opties bijkomen voor de afdelingen en/of de kostensoorten de betreffende gegevensvalidaties moeten worden aangepast.

Gegevensvalidatie met Tabellen

In plaats van een verwijzing naar de cellen B3 t/m B5 in het tabblad Param (als bron geven we dan op =Param!$B$3:$B$5) zouden we de tabelverwijzing =tblAfd[Afd] willen gebruiken (dus de kolom Afd in de tabel tblAfd).
Als we dat doen krijgen we een foutmelding, maar die kunnen we omzeilen door de functie INDIRECT te gebruiken.

Deze functie zet een tekst om in een Excel-verwijzing. Als voorbeeld: INDIRECT(“B”&A1) geeft een verwijzing naar de cel B1 als A1 de waarde 1 bevat, maar verwijst naar B2 als de waarde van A1 gelijk is aan 2 etc.

Door als bron voor de gegevensvalidatie in te voeren =INDIRECT(“tblAfd[Afd]”) krijgen we toch het gewenste resultaat.

Bekijk in het tabblad Boekingen1 de gegevensvalidatie van de cellen in kolom C en E. Deze maken gebruik van deze methode.

Voor de opvoer van boekingen in dit tabblad is ook gebruik gemaakt van een tabel. Een voordeel is dat bij de opvoer van een nieuwe regel (record) onderaan de gegevensvalidatie ook direct wordt overgenomen.
Hetzelfde geldt voor de formules in de kolommen D en F, waar met behulp van VERT.ZOEKEN de omschrijving van de gekozen afdeling of kostensoort wordt opgehaald.

In het tabblad Boekingen2 worden alleen de omschrijvingen van de afdelingen en de kostensoorten ingevoerd. Ook de invoer hiervan is via gegevensvalidatie ´beveiligd´.

Wanneer er nu een afdeling aan het tabblad Param wordt toegevoegd (bijvoorbeeld H met als omschrijving HRM) dan zullen alle gegevensvalidaties automatisch deze mogelijkheid meenemen. Uiteraard geldt dit ook voor de uitbreiding van het aantal kostensoorten.

LET OP: in het tabblad GegVal wordt HRM niet als keuzemogelijkheid getoond, omdat daar de gegevensvalidatie ‘hard’ aan de cellen B3 t/m B5 van Param  is gekoppeld.