Excel – Formulas that total across multiple sheets

microsoft excel

I am trying to make a spreadsheet to handle my monthly budgets. I have very little experience in Excel 2011 and I know there are pre-made templates already but what I need is very small in comparison.

I would like to have one file for each year, with 12 sheets within that represent each month. I want to have it keep a running total of YTD income on every month as well as other running totals. Is this possible to have a formula that keeps a running total across a variable number of sheets? I would want to be able to create a new sheet (month) and have it automatically have the previous total populated and update when I input the month's amount.

Best Answer

If you create four sheets, for example:

Totals, Jan, Feb, End

and you want your summary stats to be located on the Totals tab, you can create all your formulas as follows. In a cell on the Totals tab enter:

=sum(Jan:End!A1)

This formula will add up all the values in A1 from each sheet between Jan and End. If you insert a new sheet between Feb and End, say Mar, then your totals will update automatically.

I would probably just create all 12 months in advance, but this is another option for you.

Related Solutions

Excel – Is it possible to automatically add cells from a new sheet to a formula in Excel

Say, for instance, you have your running total in Sheet1!A1. Right, so now you create a macro you can run that will take all the totals from the other sheets and sum them.

Let's assume the totals for each sheet are on cell A10 -

Sub updatethesum()
Dim ws As Worksheet
    Dim i As Double
    i = 0
For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> "Sheet1" Then
        i = i + ws.Range("A10")
    End If

Next
Sheets("Sheet1").Range("A1") = i
End Sub

If you're worried someone will put a letter instead of a number in A10 you can restrict the input with something like this -

Sub updatethesum()

Dim ws As Worksheet
    Dim i As Double
    i = 0
    Dim bletter As Boolean

For Each ws In ThisWorkbook.Worksheets

    If ws.Name <> "Sheet1" Then
        bletter = IsNumeric(ws.Range("A10").Value)
            If bletter = True Then
             i = i + ws.Range("A10")
            End If
    End If

Next
Sheets("Sheet1").Range("A1") = i
End Sub

Best Answer

Related Solutions

Excel – Is it possible to automatically add cells from a new sheet to a formula in Excel

Related Question