Rolling 12 Month Totals from Different Worksheets

D

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
442
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
Easiest way to do something on a rolling time period is to have the date convert to value just do =value("cell date is in") then the next cell would be =Value("cell date is in" -365) that will give you the time frame for the rolling 12 months you need. In what ever formula your using to get the data then just use >=current date and <=date from a year ago. Hope the gives some help.
 
Upvote 0
Thanks Geniepants, that should work for pulling data for the rolling time period. Now how do I get Excel to look across multiple sheets and to know which columns to pull from? Some folks have had great results using the INDIRECT function in combination of some type of INDEX/MATCH, which may work in my case but cannot seem to get it started.
 
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
216
Peter_SSs
Peter_SSs
S
  • Question Question
Sum/Avg a Quarter from a full monthly Year
Replies
3
Views
579
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
438
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