Your question, your audience. Choose who sees your identity—and your question—with question security.

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

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

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

```
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Set wsTotals = Sheets.Add
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'

could be replaced with this.

=SUM('CA001'!H8:CA8)

Rob

Book1.xlsx

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?

yes separately

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.

thanks Roy, but not what I am looking for.doesn't explain what is missing from the solution.

can you make one modficiation. see attached. i need this as output.

Book1.xlsx

Book1.xlsx

```
Sub GetTotals()
Dim wsTotals As Worksheet
Dim strShName As String
Dim I As Long
Set wsTotals = Sheets.Add
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
```

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 :)

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window