De alternatieve oplossing behoeft op zich geen verdere uitwerking.
De eerstgenoemde oplossing kunnen we beter vergeten. Die is leuk als je geen gebruik kunt maken van hulpkolommen en je van Excel je hobby hebt gemaakt.
Ik zal de formules van de alternatieve oplossing nader toelichten:
'---'!BD2:
=SOM(BD1;--BA2)
De waarde van de bovenliggende cel plus BA2 numeriek gemaakt (met --): WAAR = 1 en ONWAAR = 0. --BA2 is hetzelfde als de formule in BB2: =ALS(BA2=WAAR;1;0).
Hierbij wordt gebruik gemaakt van de eigenschap dat Excel gegevens zo mogelijk numeriek maakt zodra je er mee gaat rekenen.
Bijvoorbeeld ="1"+ "1" levert gewoon 2 op. -WAAR = -1, en --WAAR = 1 (min maal min is plus). Dit schijnt een zeer efficiënte methode te zijn om gegevens numeriek te maken.
SOM werkt ook met lege en alfanumerieke gegevens die dan als 0 meetellen. BD1 is leeg.
In BD staat dus vanaf regel 2 naar beneden telkens een 0 tot aan het eerste geselecteerde artikel, dan telkens een 1 tot aan het tweede geselecteerde artikel enzovoorts.
Aandachtspunt (zoals eerder vermeld) als je bijvoorbeeld regel 12 t/m 22 verwijdert, dan staat in BD12 (de voormalige BD23): =SOM(#VERW!;--BA12)
Er stond een verwijzing naar de vorige regel, maar omdat die verwijderd is, levert dat een #VERW fout op. Remedie: formule opnieuw doortrekken naar beneden.
Als je een regel tussenvoegt (bijvoorbeeld boven regel 13 voeg je een nieuwe regel 12 toe, dan verwijst BD13 (de voormalige BD12) nog steeds naar BD11: =SOM(BD11;--BA13)
Zelfde remedie (ook om de formule in BD12 te krijgen).
Op blad '...' eigenlijk hetzelfde, alleen wordt daar de nummering voortgezet door de formule in BD1:
=MAX('---'!BD:BD)
In het vervolg van de uitleg bedoel ik met "je": Excel.
Op blad 'Totaal en rijen' ga je in B4 op zoek naar nummer 1 in BD van blad '---'.
Van het gevonden regelnummer pak je de bijbehorende waarde uit kolom '---'!B.
In B5 ga je op zoek naar nummer 2 enzovoorts totdat je het hoogste nummer van blad '---' gepasseerd bent.
Dan ga je op dezelfde wijze verder zoeken in blad '...' totdat je daar het hoogste nummer gepasseerd bent.
Verdere regels naar beneden worden leeg gelaten.
Dit vertaald in een formule in B4:
Code:
=ALS(RIJEN(B$4:B4)<=MAX('---'!BD:BD);INDEX('---'!B:B;VERGELIJKEN(RIJEN(B$4:B4);'---'!BD:BD;0));ALS(RIJEN(B$4:B4)<=MAX('...'!BD:BD);INDEX('...'!B:B;VERGELIJKEN(RIJEN(B$4:B4);'...'!BD:BD;0));""))
RIJEN(B$4:B4) levert het aantal rijen op van B4 tot en met B4 (1), 1 regel lager is dit het aantal rijen van B4 tot en met B5 (2), enzovoorts.
Dit is dus het relatieve regelnummer dat je gaat zoeken in de detailbladen en waarmee je kijkt of een maximum gepasseerd is.
Zolang dat regelnummer klener/gelijk is dan
MAX('---'!BD:BD) zoek je op blad '---'.
Met
INDEX('---'!B:B;VERGELIJKEN(RIJEN(B$4:B4);'---'!BD:BD;0)) krijg je de waarde in kolom B op de regel waar dat regelnummer gevonden is - met de functie VERGELIJKEN - in kolom BD.
Die 0 is hetzelfde als ONWAAR en dit geeft aan dat je naar de exacte waarde zoekt.
Zodra je op een relatief regelnummer boven MAX('---'!BD) uitkomt, doe je exact hetzelfde op blad '...'.
Zodra je op een relatief regelnummer komt boven MAX('...'!BD), geef je een lege waarde "" terug.
.
