Adding sums from different worksheets

[laurabell]

I am designing a yearly sales pipeline report. Each worksheet is a different
month. How do I create a running YTD total of previous months sales totals to
the current sheet I am working on?

 

[Pete_UK]

Suppose the monthly figure you want to bring to your summary sheet is
always in the same cell on the monthly sheets (eg M1). In the summary
sheet you need to list the names of the sheets - suppose these are in
A2 downwards, comprising Jan, Feb, Mar etc. Then put this formula in
B2:

=INDIRECT("'"&A2&"'!M1")

and then copy it down for your 12 months.

Hope this helps.

Pete

 

[Gord Dibben]

If you're willing to use a User Defined Function.......

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

Say you have 13 sheets, sheet1 through sheet13...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP