Update columns with summation based on criteria in multiple sheets

J

jayped

Board Regular
Joined
Mar 20, 2019
Messages
54
Hello,

I'm not sure if this is possible and I have limited knowledge of macros. Here is what I want to achieve:

I have a Summary Sheet with a list of names and ID numbers vertically and each month of the year horizontally as columns - e.g Jul 2019, Aug 2019, and so on. I will then have a tab for each month with data. A new sheet will be added at the end of each month. In each sheet, let's use 'Sep 2019' for example, will have a column containing amounts, a column containing names, and a column containing ID numbers (Columns J,K & L). I want to be able to run a macro that updates the Sep 2019 column on the Summary sheet by looking up the ID number on the Summary sheet and summing all the amounts that occurred on the Sep 2019 sheet for that ID number.



Thanks!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Yes it is possible with a macro, for this you can comment exactly where the data is:

jayped said:
Hello,

I'm not sure if this is possible and I have limited knowledge of macros. Here is what I want to achieve:

I have a Summary Sheet with a list of names (In which column?) and ID numbers (In which column?) vertically and each month of the year horizontally (In which row?) as columns - e.g Jul 2019, Aug 2019 (Is it a formatted date or is the text "Jul 2019"?), and so on.

I will then have a tab for each month with data. A new sheet will be added at the end of each month. In each sheet, let's use 'Sep 2019' for example, will have a column containing amounts, a column containing names, and a column containing ID numbers (Columns J,K & L) (In which row do the IDs begin?) .

I want to be able to run a macro that updates the Sep 2019 column on the Summary sheet by looking up the ID number on the Summary sheet and summing all the amounts that occurred on the Sep 2019 sheet for that ID number.

Thanks!
Click to expand...
 
Upvote 0
I have a Summary Sheet with a list of names (In which column? - A) and ID numbers (In which column? - B) vertically and each month of the year horizontally (In which row? - 3) as columns - e.g Jul 2019, Aug 2019 (Is it a formatted date or is the text "Jul 2019"? - "Jul 2019" for ease), and so on.

I will then have a tab for each month with data. A new sheet will be added at the end of each month. In each sheet, let's use 'Sep 2019' for example, will have a column containing amounts, a column containing names, and a column containing ID numbers (Columns J,K & L) (In which row do the IDs begin? - 2nd Row, Column L) .
 
Upvote 0
Then your data is like this:

Summary
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">NAME</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">ID</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Jul 2019</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Aug 2019</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Sep 2019</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >dam</td><td style="text-align:right; ">1</td><td style="text-align:right; ">6066</td><td style="text-align:right; ">120</td><td style="text-align:right; ">2400</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >amor</td><td style="text-align:right; ">2</td><td style="text-align:right; ">4042</td><td style="text-align:right; ">76</td><td style="text-align:right; ">1400</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >dante</td><td style="text-align:right; ">3</td><td style="text-align:right; ">4046</td><td style="text-align:right; ">84</td><td style="text-align:right; ">1800</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

Sep 2019
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >J</td><td >K</td><td >L</td><td >M</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">amounts</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Names</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Id</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td style="text-align:right; ">100</td><td >dam</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td style="text-align:right; ">200</td><td >amor</td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td style="text-align:right; ">300</td><td >dante</td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td style="text-align:right; ">400</td><td >dam</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td style="text-align:right; ">500</td><td >amor</td><td style="text-align:right; ">2</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td style="text-align:right; ">600</td><td >dante</td><td style="text-align:right; ">3</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td style="text-align:right; ">700</td><td >dam</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table>

Run this macro:


Code: 
Sub [COLOR=#0000ff]Update_Columns[/COLOR]()
  Dim sh1 As Worksheet, sh2 As Worksheet, i As Long, col As Long, s As Long
  Dim ary As Variant, f As Range
  Set sh1 = Sheets("Summary")
  ary = Array([COLOR=#a52a2a]"Jul 2019", "Aug 2019", "Sep 2019"[/COLOR]) [COLOR=#008000]'Add here the sheets you want to update.[/COLOR]
  For s = 0 To UBound(ary)
    Set sh2 = Sheets(ary(s))
    Set f = sh1.Rows(3).Find(sh2.Name, , xlValues, xlWhole)
    If Not f Is Nothing Then
      col = f.Column
      For i = 2 To sh2.Range("L" & Rows.Count).End(xlUp).Row
        Set f = sh1.Range("B:B").Find(sh2.Range("L" & i), , xlValues, xlWhole)
        If Not f Is Nothing Then
          sh1.Cells(f.Row, col) = sh1.Cells(f.Row, col) + sh2.Range("J" & i)
        End If
      Next
    End If
  Next
  MsgBox "Done"
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (Update_Columns) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0
Hi again,

I have multiple sheets again 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. Can you provide a macro for this please?

So 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.

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.


Thank you!
 
Upvote 0
jayped said:
Hi again,

I have multiple sheets again 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. Can you provide a macro for this please?

So 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.

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.


Thank you!
Click to expand...

This seems to be a new requirement.
I will gladly help you, create a new thread, it is necessary that you put sample data of what you have and what you expect as a result, it is also important that you put exactly where each data is located.
 
Upvote 0
HI. I have one request for a change to the macro above "Update_Columns".

If I want to run the macro again based on new information I don't want the calculations already calculated by the macro to be doubled or tripled. I want them deleted and recalculated. Can this be done?
 
Upvote 0
I guess deleting the target range is enough.

After this line
Code: 
Set sh1 = Sheets("Summary")

Add this line:
Code: 
sh1.Range("C4", sh1.Cells(Rows.Count, Columns.Count)).ClearContents
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
How Can I Reference multiple sheets
Replies
3
Views
118
jolivanes
jolivanes
L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
210
Lux Aeterna
L
C
  • Question Question
Transpose multiple columns to a single column
Replies
1
Views
132
Fluff
Fluff
E
  • Question Question
Write Macro to highlight based on values in another sheet
Replies
3
Views
325
Peter_SSs
Peter_SSs
X
  • Question Question
How To Create Multiple Sheets(With Criteria) From One Sheet?
Replies
1
Views
330
Kerryx
K

Forum statistics

Threads
1,224,818
Messages
6,181,151
Members
453,021
Latest member
Justyna P

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