# automate summing formula across 50 sheets

i have sheets like the attached. i need to put a formula in all the sheets

they are named in this format CA001, CA002...CA050

I need to sum the following in each book

row 8 - H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA
row 18- H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA
row 54 - H:S, T:AE, AF:AQ,AR:BC,BD:BO, BP:CA

Ideally, the sums of each could be put in a new sheet that would look like this
ROW8           ROW18              ROW54
001-
002-

OR if much easier, just put the sums of each row in rows 55-57 within each sheet that its calculated
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Group Finance ManagerCommented:
Here's a method that I have used for years, info taken from my web site

Summarise data from several Excel Worksheets…

If you have data on several sheets and need to create a summary then this example demonstrates a simple way to  create a summary sheet of the data from those sheets.

Note: The layout of the data must be exactly the same on each sheet, i. e corresponding amounts should always be in the same cell on each sheet.

For example a chain of shops has a sheet for each shop that records sales.. These sheets should be collected into one workbook, that in addition has a sheet with the same layout to hold the summary. You could use a formula like:

=Sheet1!B2 +Sheet2!B2 + Sheet3!B8

However, this formula will need updating whenever a sheet is added or removed. My example demonstrates a simple way to summarise the data that will not involve having to change formulas if you change the number of data sheets. To make this trick work you need to add two sheets, one between the summary sheet and the the first data sheet and the other sheet should be to the right of the last data sheet. In the example I have named them Top and Bottom. In the summary sheet you add formulas to sum the corresponding data like:

=SUM(Top:Bottom!B2)

In the formula Top & Bottom are the outside sheet names. So this formula will add up the amounts on each sheet between these sheets that is in B2. If you use this method to record monthly data then you can add sheets as required. Providing that you use the same layout and place the new sheet between “Top” & “Bottom” then the data will update without any changes to the formula. By moving a sheet outside of the Top & Bottom tabs, that sheet's data is no longer included in the Summary sheet.

In Excel you can move the position of a sheet by simply clicking on the relevant Worksheet tab and dragging it to a different position. This makes this trick even more useful because you can see what would happen to the summary if you move a data sheet from between between the Top & Bottom tabs. This data is then removed from the totals, so in the simple example you could see the effect of closing one of the group’s branches.

I have added a macro that adds a sheet in the specified position between Top  & Bottom. This sheet is based on a hidden Template sheet.in the workbook. The user is prompted by a Excel VBA MessageBox to provide a new name for the record sheet that is added.

VIDEO
royUK-Summarise-Data-Tip.zip
0
Author Commented:
thanks Roy, but not what I am looking for.

looking for others to weight in, thanks
0
Analyst Assistant Commented:
There's no attachment but based on what you posted you could try something like this.
``````Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long

wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")
For I = 1 To 50
strShName = "CA" & Format(I, "000")
With wsTotals
.Cells(I + 1, 1).Value = strShName
.Cells(I + 1, 2).Formula = "=SUM(" & strShName & "!H8:S8," & strShName & "!T8:AE8," & strShName & "!AF8:AQ8," & strShName & "!AR8:BC8," & strShName & "!BD8:BO8," & strShName & "!BP8:CA8)"
.Cells(I + 1, 3).Formula = "=SUM(" & strShName & "!H18:S18," & strShName & "!T18:AE18," & strShName & "!AF18:AQ18," & strShName & "!AR18:BC18," & strShName & "!BD18:BO18," & strShName & "!BP18:CA18)"
.Cells(I + 1, 4).Formula = "=SUM(" & strShName & "!H54:S54," & strShName & "!T54:AE54," & strShName & "!AF54:AQ54," & strShName & "!AR54:BC54," & strShName & "!BD54:BO54," & strShName & "!BP54:CA54)"

End With
Next I

End Sub
``````

By the way, are you sure about the ranges you wanted to sum?

I'm asking because all the columns in them are contiguous so something like this,

=SUM('CA001'!H8:S8,'CA001'!T8:AE8,'CA001'!AF8:AQ8,'CA001'!AR8:BC8,'CA001'!BD8:BO8,'CA001'!BP8:CA8)

could be replaced with this.

=SUM('CA001'!H8:CA8)
0
Author Commented:
Hi Norie, thanks! Here is the attachment (just one of the tabs - they all are in this format)

Rob
Book1.xlsx
0
Author Commented:
i do not want a contiguous sum ,as each sum is a value i am seeking
0
Analyst Assistant Commented:
Rob

Do you have a workbook with multiple sheets formatted in this way?

Or is it separate workbooks each with a single sheet with this format?

Also, do you want to sum the ranges H8:S8, T8:AE8, AF8:AQ8 separately?
0
Author Commented:
i tried your macro, it tries to open 50 files for some reason
0
Author Commented:
yes sorry i was not clear, multiple sheets formatted this way in that same name format CA001..CA050

yes separately
0
Analyst Assistant Commented:
Rob

The code I posted would expect to find 50 sheets name CA001, CA002 etc. all in the same workbook.

If the sheets are in separate workbooks a different approach will be needed.
0
Group Finance ManagerCommented:
Attach an example file. I would have thought my method would suit because it is flexible.

thanks Roy, but not what I am looking for.
doesn't explain what is missing from the solution.
0
Author Commented:
my bad Norie. I did not assemble this book...i realize it was missing certain numbered sheets, like 17, 22, 35. your code works!
0
Author Commented:
can you make one modficiation. see attached. i need this as output.
Book1.xlsx
0
Analyst Assistant Commented:
If there are missing workbooks we can add code to skip them.
``````Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long

wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")
For I = 1 To 50
strShName = "CA" & Format(I, "000")

With wsTotals

.Cells(I + 1, 1).Value = strShName
If Evaluate("ISREF('" & strShName & "'!A1)") Then
.Cells(I + 1, 2).Formula = "=SUM(" & strShName & "!H8:S8," & strShName & "!T8:AE8," & strShName & "!AF8:AQ8," & strShName & "!AR8:BC8," & strShName & "!BD8:BO8," & strShName & "!BP8:CA8)"
.Cells(I + 1, 3).Formula = "=SUM(" & strShName & "!H18:S18," & strShName & "!T18:AE18," & strShName & "!AF18:AQ18," & strShName & "!AR18:BC18," & strShName & "!BD18:BO18," & strShName & "!BP18:CA18)"
.Cells(I + 1, 4).Formula = "=SUM(" & strShName & "!H54:S54," & strShName & "!T54:AE54," & strShName & "!AF54:AQ54," & strShName & "!AR54:BC54," & strShName & "!BD54:BO54," & strShName & "!BP54:CA54)"
Else
.Cells(I + 1, 2).Resize(, 3).Value = "#N/A"
End If
End With
Next I

End Sub
``````
0
Author Commented:
yup that works better. did you get my prior message on getting the individual sums per sheet?
0
Analyst Assistant Commented:
Try this, it doesn't do the headings, that can be fixed, but it should do the formulas properly.
``````Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Dim J As Long
Dim K As Long
Dim col As Long
Dim arrCols As Variant
Dim arrRows As Variant

wsTotals.Range("B1:D1").Value = Array("ROW8", "ROW18", "ROW54")

arrCols = Array("HX:SX", "TX:AEX", "AFX:AQX", "ARX:BCX", "BDX:BOX", "BPX:CAX")
arrRows = Array(8, 18, 54)

For I = 1 To 50
strShName = "CA" & Format(I, "000")
col = 2

With wsTotals
.Cells(I + 2, 1).Value = strShName
If Evaluate("ISREF('" & strShName & "'!A1)") Then
For J = LBound(arrCols) To UBound(arrCols)
For K = LBound(arrRows) To UBound(arrRows)
.Cells(I + 2, col).Formula = "=SUM(" & strShName & "!" & Replace(arrCols(J), "X", arrRows(K)) & ")"
col = col + 1
Next K
Next J

Else
.Cells(I + 2, 2).Resize(, 3).Value = "#N/A"
End If
End With
Next I

End Sub
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
ok i am creating a part 2 if you are free. now i need to do the same thing, but set the array values for each sheet based on the value in col A

so array does not equal 8,18,54 for each sheet ...instead x,yx for each sheet are based on the following

x = where in col A this value exisits, "Total Income"
y = where in col A this value exisits, "Gross Profit"
z = where in col A this value exisits, "Net Income"

i will link to the new question in 2 hours, need to wait now for it to go live, much appreciated if you can work on it here :)
0
Author Commented:
need part 2 because i realized every sheet is not the same length in rows so revenue , profit and net income are in different rows acorss the tabs
0
Analyst Assistant Commented:
Thought that might be the case when I looked at the sample sheet.

Should be straightforward though, just replace the row values in the array with the row 'titles' and then use Match to find the appropriate row number for each title on each sheet.
0
Author Commented:
nice, thanks, can you send the code for me? i will send you a 2nd question shortly and award you there
0
Analyst Assistant Commented:
Kind of busy right now, I'll keep an eye out for the 2nd question and post there.
0
Author Commented:
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.