Add Totals Column on multiple sheets

J

jayped

Board Regular
Joined
Mar 20, 2019
Messages
54
Good day,

I have a workbook containing multiple sheets and a 'Summary' sheet. I have a column in the Summary sheet named 'Balance as Per Schedule' and I want to sum the amounts in the 'Total' columns on each sheet by using the ID No as the criteria.

Basically there is a column for 'Total' on each sheet and ID No on each of those sheets as well. I want the total of those Totals on the Summary Sheet for every ID number.

The ID Numbers are in column B from row 4 in each sheet. The "Balance as Per Schedule' column is column Q beginning row 4 as well.

The other sheets are named 'Credit Card', 'Travel Advance', etc, for example:

'Credit Card'
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]
There are many columns between ID No. and Total

'Travel Advance'
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]500.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]700.00

[/TD]
[/TR]
</tbody>[/TABLE]


The Summary Sheet will look like this:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]B/F[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Total[/TD]
[TD]Balance as Per Schedule[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]750.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1160.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you!
 
Sort by date Sort by votes

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).
 
Upvote 0
Good day,

I have a workbook containing 6 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row 8 for all sheets.

The six sheets are as follows:


'Credit Card'
A B
[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]

 
Upvote 0
Good day,

I have a workbook containing 5 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row 8 for all sheets.

The six sheets are as follows:


'Credit Card' - "Total" is contained in Column DK

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]DK[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]250.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]460.00[/TD]
[/TR]
</tbody>[/TABLE]


'Travel' - "Total" is contained in Column DG

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]DG[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]350.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]860.00
[/TD]
[/TR]
</tbody>[/TABLE]

'Petty Cash' - "Total" is contained in Column BK

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]BK[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]60.00
[/TD]
[/TR]
</tbody>[/TABLE]

'Loan' - "Total" is contained in Column BL

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]BL[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]50.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]60.00
[/TD]
[/TR]
</tbody>[/TABLE]

'Airfare' - "Total" is contained in Column BK

[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]BK[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID No.[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD]700.00[/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD]0.00
[/TD]
[/TR]
</tbody>[/TABLE]


Summary Sheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[TD="align: center"]O[/TD]
[TD="align: center"]P[/TD]
[TD="align: center"]Q[/TD]
[TD="align: center"]R[/TD]
[/TR]
[TR]
[TD]Name[/TD]
[TD]ID no.[/TD]
[TD]B/F[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Sep[/TD]
[TD]Oct[/TD]
[TD]Nov[/TD]
[TD]Dec[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]FY20[/TD]
[TD]Balance as Per Schedule[/TD]
[TD]Variance[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1300.00[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Luke[/TD]
[TD]1002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1440.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I would like the Total columns in each of the 5 sheets above be summed and placed in column Q on the summary sheet.
Is this possible?

I hope this makes some sense.


Thank you.
 
Upvote 0
jayped said:
Good day,

I have a workbook containing 5 sheets and a "summary" sheet.

In each sheet (including Summary) "Name" and "ID No." are contained in columns A and B respectively, headers are in row 5 and the first name and ID no begins row 8 for all sheets.
Click to expand...

Considerations:
- The column is located in row 5 of each sheet where the word "Total" is found.
- The data begins in row 8.

A detail you did not mention, if the ID of a sheet does not exist in the Summary sheet, what to do?

Code: 
Sub Totals_multiple_sheets()
  Dim sh As Worksheet, su As Worksheet, i As Long, f As Range, lr As Long, col As Long
  Set su = Sheets("Summary")
  For Each sh In Sheets
    If sh.Name <> su.Name Then
      For i = [COLOR=#ff0000]8[/COLOR] To sh.Range("B" & Rows.Count).End(xlUp).Row
        Set f = sh.Rows([COLOR=#ff0000]5[/COLOR]).Find("[COLOR=#ff0000]Total[/COLOR]", , xlValues, xlWhole)
        col = f.Column
        If Not f Is Nothing Then
          Set f = su.Range("B:B").Find(sh.Range("B" & i).Value, , xlValues, xlWhole)
          If Not f Is Nothing Then
            su.Range("Q" & f.Row).Value = su.Range("Q" & f.Row).Value + sh.Cells(i, col).Value          
          End If
        End If
      Next
    End If
  Next
End Sub
 
Upvote 0
Thank you for your help.

In response to your question: if the ID of a sheet does not exist in the Summary sheet, what to do? - Normally this should not happen and i can't think of what can be done in this situation.

Also, I am getting the Run-time error '91': Object variable or With block variable not set and its occuring on the line 'col = f.column'
 
Upvote 0
jayped said:
Also, I am getting the Run-time error '91': Object variable or With block variable not set and its occuring on the line 'col = f.column'
Click to expand...

That error is because in some of the sheets there is no word "Total" in row 5.
Check that you actually have the word "Total" in row 5 on the sheets and that it has no spaces left or right or any other text, as I put it in the considerations, the word should be "Total".

Try this:
Code: 
Sub Totals_multiple_sheets()
  Dim sh As Worksheet, su As Worksheet, i As Long, f As Range, lr As Long, col As Long
  Set su = Sheets("Summary")
  For Each sh In Sheets
    If sh.Name <> su.Name Then
      For i = 8 To sh.Range("B" & Rows.Count).End(xlUp).Row
        Set f = sh.Rows(5).Find("Total", , xlValues, xlWhole)
        If Not f Is Nothing Then
          col = f.Column
          If Not f Is Nothing Then
            Set f = su.Range("B:B").Find(sh.Range("B" & i).Value, , xlValues, xlWhole)
            If Not f Is Nothing Then
              su.Range("Q" & f.Row).Value = su.Range("Q" & f.Row).Value + sh.Cells(i, col).Value
            End If
          End If
        End If
      Next
    End If
  Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Populating different sheets based on the value in a column on another sheet.
Replies
3
Views
792
DanteAmor
DanteAmor
X
  • Question Question
How To Create Multiple Sheets(With Criteria) From One Sheet?
Replies
1
Views
330
Kerryx
K
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
627
Peter_SSs
Peter_SSs
H
  • Question Question
Multiple Reports Master Sheet Question
Replies
7
Views
333
awoohaw
awoohaw
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
140
Omar M
O

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top