Variabel optelbereik tussen formules

[pollekecrown]

Ik vraag me af of het mogelijk is met een formule een variabel optelbereik te creëren.

In één kolom plaats ik meerdere formules. Het aantal cellen tussen formule 1 en 2 is bijvoorbeeld vijf, het aantal cellen tussen formule 2 en 3 is vier en het aantal cellen tussen formule 3 en 4 is negen, etc. Daarbij veranderd het aantal cellen tussen de formules regelmatig, dus de vijf, vier en negen staat niet vast.

Mijn doel is dat de formule automatisch het aantal cellen optelt dat en stopt bij de volgende formule. Wie kan mij hierbij helpen?

ps: als bijlage stuur ik een voorbeeldje met extra uitleg.

 

[peter59]

Euhh??

De =SOM(.. formule of ik begrijp iets niet.

mvg Peter

 

[Thankyou]

Wat er volgens mij vergeten wordt te vertellen is:
Wordt dit ingelezen vanuit een ander bestand / wordt dit handmatig ingevoerd?
Staan de grijze vlakken altijd op dezelfde plek?
Staan de groepen altijd op volgorde?
Waarom moet er in de grijze vlakken een 0 komen als de som van een groep groter is dan 0?
Is die 0 niet de bedoeling wat dan wel?
...
...

 

[pollekecrown]

Een verdere toelichting.

De als(som.... formule is voor de vraag niet relevant. Deze heb ik alleen even in de cel gezet om aan te geven dat het erom gaat dat wanneer in één van de rijen/cellen een getal staat, dat er dan een waarde wordt weergegeven in de grijze rij.

Als je vervolgens de filter uitvoerd op 'niet lege cellen' dan blijven de grijze rijen staan van de groep waarin een artikel is geselecteerd, alsmede het geselecteerde artikel zelf. Groepen waarbinnen geen product wordt geselecteerd worden verborgen door de filter. Het uiteindelijke doel is dat medewerkers zo meerdere producten handmatig uit een lange lijst kunnen selecteren en dat het filter ervoor zorgt dat de geselecteerde producten in een korte overzichtelijke lijst worden weergegeven.

- In elke grijze rij komt een formule te staan.
- De formule telt alle waarden tussen de formule zelf en de volgende formule in de kolom, onafhankelijk van hoeveel waarden/cellen/rijen dit zijn
- De formule moet de waarde optellen om te zien of deze waarde > 0 is.
- is de waarde >0 dan moet er in de grijze cel een waarde worden weergegeven
- In de grijze cellen mag elke willekeurige waarde komen. (dit is niet relevant voor de vraag)
- Het aantal cellen (of rijen) tussen de grijze is telkens anders en kan toenemen en afnemen (dit is het belangrijkste element van de vraag)

De vraag is om een variabel bereik te selecteren tussen twee formules. Met variabel bedoel ik dat het aantal cellen tussen twee formules telkens anders is. De formule moet dus de ene keer 4 cellen tellen en een andere keer 8 cellen, dit zonder dat de formule aangepast moet worden.

Hopelijk wordt de vraag zo iets duidelijker.

 

[Thankyou]

Het aantal cellen (of rijen) tussen de grijze is telkens anders en kan toenemen en afnemen (dit is het belangrijkste element van de vraag)

Mijn vraag is dan nog: Om een groter aantal artikelen onder een groep te brengen, gebruik je daar "rijen invoegen" voor? en verwijder je dan ook komplete rijen als het minder artikelen worden?

 

[pollekecrown]

Dit klopt.

Bij een formule met een vast bereik zou dit inhouden dat er een risico bestaat dat de formule niet meer goed werkt (bijvoorbeeld als er een regel wordt ingevoegd boven een grijze lijn). Aangezien ik dit verwijderen en toevoegen niet zelf zal doen, is dit risico groot.

Daarnaast moet ik een rekensheet maken voor meerdere producten. Elke cel afzonderlijk de formule aanmaken is dan erg arbeidsintentief en foutgevoelig. Vandaar dat ik graag een formule wil die automatisch het aantal cellen/waarden telt tussen twee formules in dezelfde kolom. Gemak dient de mens zie je en één keer uitzoeken is in dit kader een levenlang plezier

 

[Thankyou]

Probeer dan eens in C3 van je voorbeeldbestand:

=SOM(INDIRECT("[COLOR="red"]C4[/COLOR]"&":"&ADRES(VERGELIJKEN("[COLOR="red"]Groep 2[/COLOR]";B2:B37;0);3)))

of in het engels:
=SUM(INDIRECT("[COLOR="red"]C4[/COLOR]"&":"&ADDRESS(MATCH("[COLOR="red"]Groep 2[/COLOR]",B2:B37,0),3)))

Rode gedeelten zal je per formule moeten aanpassen.

Succes,

 

[RB_online]

Wellicht dat het gebruik van namen je kan helpen.
Een bereik met een naam past zich aan als je een rij invoegd of verwijderd.

Mvg,
René
Bekijk bijlage Variabel optelbereik tussen formules.xls

 

[pollekecrown]

Thankyou en RB_online. jullie oplossingen betekenen nog steeds dat je veelvuldig handmatig formules moet aanpassen en dit is nu juist wat ik wil voorkomen. Vandaar dat ik wil dat de formule zelf bepaald hoeveel cellen er zijn tussen hem en de volgende formule.

 

[Thankyou]

De formule die ik je gegeven hebt hoef je maar 1x in te geven, per "groep" aan te passen en daarna niet meer, zolang de grijze rijen maar niet gedelete worden. De formule zoekt naar het celadres waar de volgende groep staat.

Succes,

 

[RB_online]

Voor namen geldt hetzelfde.
Naam aanmaken, in je formule verwijzen naar de naam en als het bereik daarna veranderd past de gedefiniëerde naam zichzelf aan naar het nieuwe bereik.
Bijvoorbeeld
Naam: groep1 is bereik C4:C7
als je nu een rij invoegd wordt het bereik van groep1 automatisch C4:C8.
controleer maar.
De formule in het voorbeeld bestand in C3 moet je wel veranderen naar

=ALS(SOM(groep1)>0;0;"")

Kijk nog maar eens naar het voorbeeld bestand bij mijn vorige post.

Mvg,

René

 

[HSV]

Of:
in C3 en in elke grijze vak plakken.

=ALS(SOM(INDIRECT(ADRES(RIJ()+1;3)&":"&ADRES(VERGELIJKEN(LINKS(INDIRECT("B"&RIJ());5)&" "&AANTALARG($A$3:A8)+1;$B$1:$B$38;0)-1;3)))>0;0;"")

of:

=ALS(SOM(INDIRECT(ADRES(RIJ()+1;3)&":"&ADRES(VERGELIJKEN("groep "&AANTALARG($A$3:A3)+1;$B$1:$B$38;0)-1;3)))>0;0;"")

en zet in B35 'Groep 6'

 

[WHER]

Of deze:

 

[sylvester-ponte]

hoi polleke

met een eigen gemaakte formule kan het ook

dit voorbeeld berekend de som tot de volgende lager gelegen cel met een kleur

Function OptellenTotVolgendeGekleurdeVeld(Startpunt As Range)
    Dim Eindpunt As Range, T, MaxRij As Long
    MaxRij = ActiveSheet.UsedRange.Rows.Count
    Set Eindpunt = Startpunt
    Do Until Eindpunt.Interior.ColorIndex <> -4142 Or Eindpunt.Row > MaxRij
        Set Eindpunt = Eindpunt.Offset(1, 0)
    Loop
    OptellenTotVolgendeGekleurdeVeld = Application.WorksheetFunction.Sum(Range(Startpunt, Eindpunt.Offset(-1, 0)))
End Function

dit heb ik toegevoegd om herberekening af te dwingen

Private Sub Worksheet_Change(ByVal Target As Range)
Application.CalculateFull
End Sub

groet sylvester

 

[pollekecrown]

Hoi HSV en WHER. Bedankt voor jullie formules. In principe zijn deze perfect, alleen in mijn uiteindelijk sheet niet te gebruiken. Tussen de groepen staat namelijk ook informatie, dus kun je niet zoeken naar de eerste gevulde cel en hieraan referen. Toch zijn beide zeer interessante opties die in andere gevallen zeker gaan helpen. Bedankt.

Sylvester, wat een super oplossing om zelf een formule te schrijven en deze te gebruiken. Ik heb hem gekopiëerd en gebruikt in een =als() functie en het werkt als een zonnetje. Ik heb nog wel een vraag: je gebruikt nu als referentie de volgende gearceerde cel. Zou de formule ook kunnen kijken naar de eerste cel die een formule bevat? Als dit kan dan is de oplossing helemaal perfect. (wat niet betekent dat je huidige oplossing niet werk )

 

[pollekecrown]

Aanvulling: of naar de volgende cel die een andere opmaak heeft dan "getal" , bijvoorbeeld "standaard" of "tekst" dus.

 

[sylvester-ponte]

hoi
deze function reageert op formules:

Function OptellenTotVolgendeFormule(Startpunt As Range)
    Dim Eindpunt As Range, T, MaxRij As Long
    MaxRij = ActiveSheet.UsedRange.Rows.Count
    Set Eindpunt = Startpunt
    Do Until Eindpunt.HasFormula Or Eindpunt.Row > MaxRij
        Set Eindpunt = Eindpunt.Offset(1, 0)
    Loop
    OptellenTotVolgendeFormule= Application.WorksheetFunction.Sum(Range(Startpunt, Eindpunt.Offset(-1, 0)))
End Function

groet sylvester

 

[pollekecrown]

Hoi Sylvester,

Zo is hij helemaal goed. Super bedankt voor je hulp.

Groet Paul