[EXCEL] Formule voor alle werkbladen

[Ebbie73]

Ik heb een bestandje aangemaakt waarbij per maand in en uitgaves worden bijgehouden.
Daarbij is elk werkblad een maand.
Het laatste werkblad is een totaalplaatje van alle maanden bij elkaar
Bij het totaalplaatje staat in een bepaalde cel de volgende formule: =SOM('Mei 2006'!N8;'Juni 2006'!N8;'Juli 2006'!)
Hiermee tel ik alle afgelopen maande bij elkaar op.
Als ik vervolgens een werkblad 'Augustus 2006' wil toevoegen, moet die maand ook weer bij de formule in cel van het totaalplaatje ingevoegd worden. en dan nog eens 40 keer.

Is er een formule die de waarden van N8 van elk werkblad bij elkaar optelt en ook de nog toe te voegen werkbladen daarbij meeneemt?

ik hoop dat ik de vraag een beetje duidelijk geformuleerd heb...

ik heb al in dit forum gezocht en ook gegoogled. no specific result.

[Daos]

Je kan in VBA gewoon je eigen functies maken.

Zet in een Module bijvoorbeeld dit:

Visual Basic:

Function SomVanAlleN8()
    Application.Volatile True
    
    som = 0
    For Each ws In ThisWorkbook.Worksheets
        som = som + ws.Range("N8")
    Next
    
    SomVanAlleN8 = som
End Function

In de excel-cel zet je dan gewoon =SomVanAlleN8().

[ Voor 25% gewijzigd door Daos op 27-06-2006 11:56 ]

[Ebbie73]

@ Daos

Het werkt perfect, bedankt!

ik heb alleen 40x die modules moeten aanmaken, maar dat hoeft gelukkig niet meer per maand.

worden die modules opgeslagen in hetzelfde bestand? of worden deze toegevoegd aan Excel in het algemeen? of allebei..?

[Daos]

Ebbie73 schreef op dinsdag 27 juni 2006 @ 13:26:
ik heb alleen 40x die modules moeten aanmaken, maar dat hoeft gelukkig niet meer per maand.

40x?

- Je kan gewoon meerdere functies in 1 module zetten.

- Je kan ook gewoon een argument meegeven. Zoiets:

Visual Basic:

Function SomVanAlle(cel)
    Application.Volatile True
    
    som = 0
    For Each ws In ThisWorkbook.Worksheets
        som = som + ws.Range(cel.Address)
    Next
     
    SomVanAlle = som
End Function

In een cel komt dan: =SomVanAlle(N8)

worden die modules opgeslagen in hetzelfde bestand? of worden deze toegevoegd aan Excel in het algemeen? of allebei..?

Gewoon in het Excel-bestand.

[Ebbie73]

nou werkt het helemaal goed.

ik kom er nou wel achter dat ik heel weinig weet van excel of VBA, maar dit helpt mij een heel stuk verder.

nogmaals bedankt!

edit: het werkte goed totdat ik die 40 ging verwijderen...

[ Voor 17% gewijzigd door Ebbie73 op 27-06-2006 15:19 ]

[Daos]

Ebbie73 schreef op dinsdag 27 juni 2006 @ 14:40:
nou werkt het helemaal goed.

Ik denk het niet.

De "For Each ws In ThisWorkbook.Worksheets" gaat alle werkbladen langs. Als je alleen bij bepaalde werkbladen iets wilt doen, dan kan je daar voor zorgen met een If-statement. Bv

Visual Basic:

Function SomVanAlle(cel, jaar)
    Application.Volatile True
    
    som = 0
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If InJaar(ws, jaar) And Not IsOverzicht(ws) Then
            som = som + ws.Range(cel.Address)
        End If
    Next
     
    SomVanAlle = som
End Function

Function InJaar(ws As Worksheet, jaar) As Boolean
    InJaar = EndsWith(ws.Name, Str(jaar))
End Function

Function IsOverzicht(ws As Worksheet) As Boolean
    IsOverzicht = StartsWith(ws.Name, "Totaal")
End Function


Function StartsWith(s1 As String, s2 As String) As Boolean
    StartsWith = Left(s1, Len(s2)) = s2
End Function

Function EndsWith(s1 As String, s2 As String) As Boolean
    EndsWith = Right(s1, Len(s2)) = s2
End Function

Het jaar kan je meegeven als getal, maar ook uit een andere cel halen. Bv =SomVanAlle(N8;2006) of =SomVanAlle(N8;$A$1) ($'s zorgen ervoor dat het adres niet verandert bij kopieren naar andere cel).

[onkl]

Daos schreef op dinsdag 27 juni 2006 @ 14:49:
[...]

Ik denk het niet.

Maak het dan wel optioneel

Visual Basic:

Function SomVanAlle(ByVal cel As Range, Optional ByVal jaar As Integer = 0)
    Application.Volatile True
    Dim geenjaar As Boolean
    If jaar = 0 Then geenjaar = True
    som = 0
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If geenjaar And Not IsOverzicht(ws) Then
            som = som + ws.Range(cel.Address)
        Else
            If InJaar(ws, jaar) Then
                som = som + ws.Range(cel.Address)
            End If
        End If
    Next

    SomVanAlle = som
End Function

Function InJaar(ws As Worksheet, jaar) As Boolean
    InJaar = EndsWith(ws.Name, Str(jaar))
End Function

Function IsOverzicht(ws As Worksheet) As Boolean
    IsOverzicht = StartsWith(ws.Name, "Totaal")
End Function


Function StartsWith(s1 As String, s2 As String) As Boolean
    StartsWith = Left(s1, Len(s2)) = s2
End Function

Function EndsWith(s1 As String, s2 As String) As Boolean
    EndsWith = Right(s1, Len(s2)) = s2
End Function

[Daos]

Ebbie73 schreef op dinsdag 27 juni 2006 @ 14:40:
edit: het werkte goed totdat ik die 40 ging verwijderen...

Wat is het probleem?

offtopic:
Een edit van je post valt niet zo erg op als er al wat posts van anderen achter staan.

[Ebbie73]

Toen ik deze in voerde werkte het het in combinatie met de eerste wel, maar toen ik die 40 van de eerste weghaalde, werkte deze niet meer...

Daos schreef op dinsdag 27 juni 2006 @ 14:23:

Visual Basic:

Function SomVanAlle(cel)
    Application.Volatile True
    
    som = 0
    For Each ws In ThisWorkbook.Worksheets
        som = som + ws.Range(cel.Address)
    Next
     
    SomVanAlle = som
End Function

Ik moest namelijk in een range van N3 t/m N22 en O3 t/m O22 deze formule aanmaken dus dat waren die 40.

ik heb uiteindelijk de laatst toegevoegde van onkl ingevoerd. die doet het bij mij zoals het moet...

Daos en onkl: Bedankt!

[onkl]

Je moet dus ook de functies in je werkblad aanpassen.
=somvanalleN8()
wordt:
=somvanalle(N8)
[edit]en soms ben je te laat Maar het werkt, en daar gaat 't om.

[ Voor 27% gewijzigd door onkl op 27-06-2006 16:08 ]

[KingRichard]

Ik heb Excel niet bij de hand, dus ik kan je niet precies uitleggen hoe het moet. Maar ik weet zeker dat er een manier is om dit zonder VBA te doen. Selecteer Som in formule browser en als je de range moet selecteren, selecteer je eerst de werkbladen die je wilt hebben. Daarna klik je op de juiste cel en klaar is Ebbie. Zoiets in ieder geval...

[Verwijderd]

KingRichard schreef op dinsdag 27 juni 2006 @ 23:35:
Ik heb Excel niet bij de hand, dus ik kan je niet precies uitleggen hoe het moet. Maar ik weet zeker dat er een manier is om dit zonder VBA te doen. Selecteer Som in formule browser en als je de range moet selecteren, selecteer je eerst de werkbladen die je wilt hebben. Daarna klik je op de juiste cel en klaar is Ebbie. Zoiets in ieder geval...

zoiets als =som(blad1:Blad12!N8) werkt natuurlijk, maar deze formule is niet dynamisch, als er een blad bijkomt, klopt de som niet meer. in principe kan het zonder vba mogelijk zijn dmv een in een naam ingebedde xlm (oude excelmacrotaal) die de lijst met bladen dynamisch kan bijhouden in een arrayformule, maar dan ben je het toch nodeloos complex aan het maken. in dit geval zullen we het maar bij vba houden, de voorgestelde oplossing ziet er netjes uit.

[Montana]

Ik maak bij het maken van zoiets, waarbij ik niet weet hoeveel tabbladen
er uiteindelijk zullen worden,

Een rekenblad aan / daarnaast een tab met de naam A en een met de Naam Z
( kan ook met cijfers) daarna gewoon met = som de juiste cel pakken zowel in Tab
A en tab Z. Voeg ik later tabs toe en ze staan tussen A en Z dan wordt de telling gewoon
meegemomen zonder verdere aanpassingen.