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
Related videos on Youtube
Comments
-
Tom Wilkinson over 1 year
I would like to make a new financial spreadsheet for tracking expenses. Since the expense sheets are created as and when I cannot pre-generate a spreadsheet to populate with formulae. I have a summary sheet which adds totals from the other sheets and currently create a formula by hand at the end of the month. Is there a way to automatically sum across multiple sheets and add new sheets to the sum when they are created?
Is this possible using standard Excel or am I going to need to use some VBA?
I know how to add the same cell across multiple sheets already (see: Excel - Formulas that total across multiple sheets?)
This vba script is part of the way to what I am looking to do but not exactly what I want to do: Excel - import data from cell automatically when a new tab is created
P.S. I don't know VBA but willing to learn. I'm used to C/Python.
-
DesertPride about 9 yearsIf you are summing "cookie-cutter" ranges then you could insert sheets named "begin" and "end" before and after all the sheets you wish to consolidate, then use =SUM(begin:end!A1), and make sure that you always add sheets in between the "begin" and "end" sheets.
-
Tom Wilkinson about 9 yearsThat is what I was considering as a quick and easy solution. Probably what I will implement for March. Still hoping there would be a way to automate most of this. Essentially I want to make a fairly bullet proof version for an end user that I rarely have to edit.
-
Raystafarian about 9 yearsWhy doesn't that solution work for you? Are your totals on each sheet in different locations?
-
Tom Wilkinson about 9 yearsI'm new to VBA - can you point me in the direction of the correct resources or provide a working script?
-
Raystafarian about 9 yearsIt depends on whether you want it to change when you tell it to or change when it happens. I'd recommend you code it to a button.
-
Tom Wilkinson about 9 yearsA button that creates the new sheet?
-
Tom Wilkinson about 9 yearsSo something like a "Calculate!" button that trawls through all sheets with a certain prefix and makes a sum based on them?
-
-
Tom Wilkinson about 9 yearsWorks like a charm! Now to test it on my end users
-
Raystafarian about 9 years@tom just a note, this doesn't leave any evidence that the total sum is correct. You could, instead, have i be a string that continuously adds each sheet as a reference and insert it as a formula, then you'd have evidence.