Waarom werkt het soms wel en soms niet?

In een training krijg ik vaak een melding: “bij mij doet ie het niet” of “bij mijn collega werkt het wel en bij mij niet” (of andersom).
In dit blog laat ik twee voorbeelden hiervan zien. En natuurlijk een verklaring (want meestal is die er wel!)

Bij SOM wordt het gebied wel/niet vanzelf uitgebreid

In een bestand is de SOM-functie gebruikt voor een totaaltelling.
Bij de ene gebruiker gebeurt er bij het tussenvoegen van een nieuwe rij het een en bij een andere gebruiker gebeurt in hetzelfde bestand iets anders.
Hoe kan dat?

Voor deze uitleg gebruik ik het volgende voorbeeld.

Gebruiker 1

• In een bereik zijn in B3:B7 een aantal getallen ingevuld. Zie ① in de afbeelding hieronder.
  Daaronder in B8 staat een optelling van die getallen met een SOM-functie: =SOM(B3:B7).
• Vervolgens wordt er onder rij 7 een nieuwe lege rij ingevoerd. Je ziet de nieuwe situatie bij ②. De berekening van het totaal is niet gewijzigd. Die staat nu in B9 en is nog steeds =SOM(B3:B7).
• Zodra je een getal typt in B8 wordt de formule van de optelling gewijzigd in =SOM(B3:B8). Zie situatie ③.
  Met andere woorden: de formule neemt automatisch de inhoud van de nieuwe ingevoegde cel mee.

Gebruiker 2
De startsituatie is hetzelfde als hierboven: ①.

• Na het invoegen van de rij is de situatie nog steeds hetzelfde: ②.
• Maar nu wordt bij het invoegen van een getal in B8 de formule van de optelling niet gewijzigd: ③.
  Die blijft hetzelfde!
  Omdat hier nu een aangrenzende waarde niet wordt meegenomen, verschijnt er een groene driehoek in die cel en een waarschuwingspictogram (als de cel is geselecteerd).

Rara, hoe kan dat?

Dit heeft te maken met een instelling in Excel. De instellingen kun je vinden (en aanpassen) bij Bestand > Opties.
Je gaat naar de rubriek Geavanceerd en het onderdeel Opties voor bewerken.

Als Opmaak en formules van gegevensbereiken doorvoeren is INgeschakeld, krijg je de situatie als bij gebruiker 1. Als dit is uitgeschakeld krijg je de situatie van gebruiker 2.

Dit is dus een persoonlijke en niet een bestand-gebonden instelling!
Daarom kan dit in hetzelfde bestand bij de een anders uitpakken dan bij de ander!

Bij Validatie wordt het gebied wel/niet vanzelf uitgebreid

Hetzelfde probleem, maar dan door een heel andere oorzaak.
In onderstaand voorbeeld is gegevensvalidatie toegepast cel B5. Als je die cel selecteert zie je dat er een keuzeknop achter verschijnt (pijl). Als je op die keuzeknop klikt, zie je de opties waaruit je kunt kiezen: Artikel 1 t/m Artikel 6.

Situatie 1

Om die gegevensvalidatie te kunnen maken is een lijst gemaakt met de verschillende keuzemogelijkheden. Om te zorgen dat dit automatisch uitbreidt bij nieuwe artikelen in die lijst, is er een tabel van gemaakt (Invoegen > Tabel).

Daarna is de gegevensvalidatie bij die cel gemaakt met Gegevens > Gegevensvalidatie.
Bij Toestaan is gekozen voor Lijst ① en de Bron voor die items in die lijst (die tabel die hiervoor is genoemd) is $P$3:$P$8 ②.

Nu wordt er aan de tabel een artikel toegevoegd aan het einde ②. Je ziet dat de keuzelijst van B5 automatisch ook die nieuwe keuze biedt ①. Dat komt omdat er verwezen wordt naar een kolom in een tabel. Die tabel is uitgebreid en dus de keuzelijst van B5 ook.
Je ziet dat als je de gegevensvalidatie bekijkt: die loopt nu tot P9 ③.

Situatie 2

Hierbij is ook een lijst gemaakt en is van die lijst een tabel gemaakt, maar het verschil is de plaats waar die tabel staat. Deze tabel staat niet op hetzelfde werkblad als de validatie, maar op een ander werkblad in dat bestand.

Je ziet dat bij de gegevensvalidatie: er wordt verwezen naar de cellen A4:A9 op het werkblad met de naam Variabelen.

Als de tabel nu uitbreidt met een nieuw artikel ②, wordt de validatie bij B5 niet uitgebreid ①. Bekijk je de validatie ③ dan is het niet aangepast!

Rara, hoe kan dat?

Dat heeft te maken met de plaats van de tabel:

• De tabel waar de gegevensvalidatie naar verwijst staat op hetzelfde werkblad als de cel met de validatie: de validatie wordt automatisch uitgebreid als de tabel groter wordt.
• De tabel waar de gegevensvalidatie naar verwijst staat op een ander werkblad als de cel met de validatie: de validatie wordt niet automatisch uitgebreid als de tabel groter wordt.

Kun je dat wel werkend krijgen?

Je kunt de validatie waarvan de tabel op een ander werkblad staat automatisch uitbreidbaar maken, als je de gegevens daar een naam geeft. Die naam gebruik je dan in de validatiebron.

• Stap 1:
  • Selecteer hiervoor de kolomgegevens van de artikelen (A4:A9).
  • Klik vervolgens in het naamvak en typ de gewenste naam (hier Productenlijst).
  • Bevestig dat met Enter (niet vergeten!!)
• Stap 2:
  • Gebruik bij de gegevensvalidatie als Bron de naam die je hebt toegekend.