Rolling 12 Month Totals from Different Worksheets

[dwgnome]

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
2	Day	Jan 13	*	Feb 13	*	May 13	*	Jun 13
3	1	1438.0	0.1	1449.8	0.6	*	*	*
4	2	1439.0	1.0	1450.0	0.2	*	*	*
5	3	1439.2	0.2	1450.7	0.7	*	*	*
6	4	1439.4	0.2	1450.7	0.0	*	*	*
33	31	1449.2	0.0	*	*	*	*	*
34	*	*	*	*	*	*	*	*
35	MONTH TOTAL	11.3	*	1.5	*	0.0	*
36	*	*	*	*	*	*
37	*	*	*	*	*	*
38	*	*	*	*	*	*
39	*	*	*	*	*	*	*	*
40	*	*	*	*	*	*	12-months before:	1/1/2013

<tbody>
</tbody>

AS0904-2012

*	B	C	D	E	F	G	H	Y	Z
2	Day	Jan 12	*	Feb 12	*	Mar 12	*	Dec 12	*
3	1	1339.0	0.6	1344.3	0.0	1345.4	0.0	1430.7	0.0
4	2	1339.5	0.5	1344.3	0.0	1345.4	0.0	1431.0	0.3
29	27	1344.0	0.0	1345.4	0.0	1354.1	0.4	1436.5	0.6
30	28	1344.1	0.1	1345.4	0.0	1354.1	0.0	1437.0	0.5
31	29	1344.2	0.1	*	*	1354.5	0.4	1437.3	0.3
32	30	1344.3	0.1	*	*	1354.6	0.1	1437.3	0.0
33	31	1344.3	0.0	*	*	1354.8	0.2	1437.9	0.6
34	*	*	*	*	*	*	*	*	*
35	MONTH TOTAL	5.9	*	1.1	*	9.4	*	7.2
36	*	*	*	*	*	*	*	*	*
37	*	*	*	*	*	Current Year Total:	99.5	*	*

<tbody>
</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.

 

[dwgnome]

Bump. Need some help on this one.

 

[Oeldere]

I should solve this:

make one consolidated sheet.

add (with a macro) all data to that sheet.

use the headers in the new sheet, for the data in the pivot table.

in pivot table it is possible to make running totals (I seen that before).

 

[dwgnome]

Hi Oeldere,

I cannot change the way the data is presented. So I still need a solution as requested.

 

[Oeldere]

I cannot change the way the data is presented.

Why not?

In my suggestion under #3 you don't change the way the data us presented.

 

[dwgnome]

I prefer not to have to summarize/copy data to a new sheet. Also I do not want to use pivot tables as a solution.

Can someone show me a formula way to grab data in the appropriate column using sheet names to step through the 12-month rolling period. I have used INDIRECT before along with list of sheet names and cycled through that way.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump.

 

[dwgnome]

Bump