Rolling 12 Month Totals from Different Worksheets

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
Looking for a way to create a 12-month rolling total in which the totals are not only in the current worksheet but from other worksheets. The worksheets are setup identical and the tab names have a name and year (i.e., AS0904-2012). Each worksheet represents a calendar year. The totals are in every other column and the headers have dates but offset by one column to the left of the totals. See example below.

On one of the worksheets at M40 I have a drop down cell that provides the last month in the 12-month period, then excel is to extract the values from the corresponding worksheets and columns. The date headings are actual dates such as 1/1/2012, 2/1/2012 displayed as month year.

So for the 12 month rolling total ending in January 2013, the values should be taken from worksheet AS0904-2012 starting with February (F35), through December (Z35) and on AS0904-2013, January (D35).

The totals represent the number of hours the units were in operation, while the larger numbers represent the cumulative hour meter values.


AS0904-2013

*
B
C
D
E
F
K
L
M
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"] 2
[/TD]
[TD="bgcolor: #FFFF99"] Day
[/TD]
[TD="bgcolor: #FFFF99"] Jan 13
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] Feb 13
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] May 13
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] Jun 13
[/TD]

[TD="bgcolor: #CACACA, align: center"] 3
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 1
[/TD]
[TD="align: center"] 1438.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.1
[/TD]
[TD="align: center"] 1449.8
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.6
[/TD]

[TD="bgcolor: #CCFFFF"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 4
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 2
[/TD]
[TD="align: center"] 1439.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 1.0
[/TD]
[TD="align: center"] 1450.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.2
[/TD]

[TD="bgcolor: #CCFFFF"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 5
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 3
[/TD]
[TD="align: center"] 1439.2
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.2
[/TD]
[TD="align: center"] 1450.7
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.7
[/TD]

[TD="bgcolor: #CCFFFF"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 6
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 4
[/TD]
[TD="align: center"] 1439.4
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.2
[/TD]
[TD="align: center"] 1450.7
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]

[TD="bgcolor: #CCFFFF"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 33
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 31
[/TD]
[TD="align: center"] 1449.2
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="bgcolor: #C0C0C0"] *
[/TD]
[TD="bgcolor: #969696"] *
[/TD]

[TD="bgcolor: #CCFFFF"] *
[/TD]
[TD="bgcolor: #C0C0C0"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 34
[/TD]

[TD="bgcolor: #CACACA, align: center"] 35
[/TD]
[TD="colspan: 2, align: center"] MONTH TOTAL
[/TD]
[TD="bgcolor: #FFCC00, align: center"] 11.3
[/TD]

[TD="bgcolor: #FFCC00, align: center"] 1.5
[/TD]

[TD="bgcolor: #FFCC00, align: center"] 0.0
[/TD]

[TD="bgcolor: #CACACA, align: center"] 36
[/TD]

[TD="bgcolor: #CACACA, align: center"] 37
[/TD]

[TD="bgcolor: #CC99FF"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 38
[/TD]

[TD="bgcolor: #CACACA, align: center"] 39
[/TD]

[TD="bgcolor: #CACACA, align: center"] 40
[/TD]

[TD="align: right"] 12-months before:
[/TD]
[TD="bgcolor: #CC99FF, align: center"] 1/1/2013
[/TD]

</tbody>


AS0904-2012

*
B
C
D
E
F
G
H
Y
Z
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*
*

<tbody>
[TD="bgcolor: #CACACA, align: center"] 2
[/TD]
[TD="bgcolor: #FFFF99"] Day
[/TD]
[TD="bgcolor: #FFFF99"] Jan 12
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] Feb 12
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] Mar 12
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]
[TD="bgcolor: #FFFF99"] Dec 12
[/TD]
[TD="bgcolor: #FFFF99"] *
[/TD]

[TD="bgcolor: #CACACA, align: center"] 3
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 1
[/TD]
[TD="align: center"] 1339.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.6
[/TD]
[TD="align: center"] 1344.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1345.4
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1430.7
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]

[TD="bgcolor: #CACACA, align: center"] 4
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 2
[/TD]
[TD="align: center"] 1339.5
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.5
[/TD]
[TD="align: center"] 1344.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1345.4
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1431.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.3
[/TD]

[TD="bgcolor: #CACACA, align: center"] 29
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 27
[/TD]
[TD="align: center"] 1344.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1345.4
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1354.1
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.4
[/TD]
[TD="align: center"] 1436.5
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.6
[/TD]

[TD="bgcolor: #CACACA, align: center"] 30
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 28
[/TD]
[TD="align: center"] 1344.1
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.1
[/TD]
[TD="align: center"] 1345.4
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1354.1
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="align: center"] 1437.0
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.5
[/TD]

[TD="bgcolor: #CACACA, align: center"] 31
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 29
[/TD]
[TD="align: center"] 1344.2
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.1
[/TD]
[TD="bgcolor: #C0C0C0"] *
[/TD]
[TD="bgcolor: #969696"] *
[/TD]
[TD="align: center"] 1354.5
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.4
[/TD]
[TD="align: center"] 1437.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.3
[/TD]

[TD="bgcolor: #CACACA, align: center"] 32
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 30
[/TD]
[TD="align: center"] 1344.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.1
[/TD]
[TD="bgcolor: #C0C0C0"] *
[/TD]
[TD="bgcolor: #969696"] *
[/TD]
[TD="align: center"] 1354.6
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.1
[/TD]
[TD="align: center"] 1437.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]

[TD="bgcolor: #CACACA, align: center"] 33
[/TD]
[TD="bgcolor: #CCCCFF, align: center"] 31
[/TD]
[TD="align: center"] 1344.3
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.0
[/TD]
[TD="bgcolor: #C0C0C0"] *
[/TD]
[TD="bgcolor: #969696"] *
[/TD]
[TD="align: center"] 1354.8
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.2
[/TD]
[TD="align: center"] 1437.9
[/TD]
[TD="bgcolor: #CCFFFF, align: center"] 0.6
[/TD]

[TD="bgcolor: #CACACA, align: center"] 34
[/TD]

[TD="bgcolor: #CACACA, align: center"] 35
[/TD]
[TD="colspan: 2, align: center"] MONTH TOTAL
[/TD]
[TD="bgcolor: #FFCC00, align: center"] 5.9
[/TD]

[TD="bgcolor: #FFCC00, align: center"] 1.1
[/TD]

[TD="bgcolor: #FFCC00, align: center"] 9.4
[/TD]

[TD="bgcolor: #FFCC00, align: center"] 7.2
[/TD]

[TD="bgcolor: #CACACA, align: center"] 36
[/TD]

[TD="bgcolor: #CACACA, align: center"] 37
[/TD]

[TD="align: right"] Current Year Total:
[/TD]
[TD="bgcolor: #99CC00, align: center"] 99.5
[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4

So in this case, the 12-month rolling total will be all of 2012 minus Jan 12 total plus Jan 13 total. 99.5 - 5.9 + 11.3 = 104.9

Any help would be appreciated. Excel 2003.
 
Sort by date Sort by votes
Is it feasible to have a "master sheet" that contains all the dates and numbers? I had a similar project and found the easiest way (so far) was to just re-arrange the data on a new sheet so that column A was the date, column b was the data. Then you can just is sumifs to get the rolling total. Not sure if that would work in your case, but since the info from previous years shouldn't change you could set something to add the new data when the worksheet was opened. I'm not sure exactly on the code, I'm pretty new to VBA but that would seem to be the easiest route.
 
Upvote 0
It may be feasible, but all worksheets are fixed in the format they are in. It should be a matter of getting a single formula to sum across the one row matching the month above.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
2 worksheets one rolling 12 month sum
Replies
2
Views
288
Shazzaz
S
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
212
Peter_SSs
Peter_SSs
S
  • Question Question
Sum/Avg a Quarter from a full monthly Year
Replies
3
Views
577
myall_blues
myall_blues
D
  • Question Question
Find names in one sheet (cells A2:A33) from 12 different sheets and add the total (column are not adjacent) that is in another column to another sheet
Replies
1
Views
437
Fluff
Fluff
V
  • Question Question
VBA to copy column based on a header matching a cell value (date). The paste formulas into another column. Finally, hard code values after copying.
Replies
2
Views
4K
vekdas
V

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