Macro for Updating Cumulative Totals Monthly

[NikolaB]

Running Windows XP - Excel2007

Each month I prepare financial spreadsheets that include entries for that month and a running cummulative total for the year. I add the current month to the cumulative total last month (by referencing the same cell in last months spreadsheet).

Each month I have to change the file reference to the most recent month in order to have the running total to sum with the current month. Because of the large number of entries, I would really appreciate some help with a macro that might automate the shifting of the referenced file. Below is a very simplified sample of what I am working with.

Many Thanks!

Excel Workbook
	A	B	C	D
1	Cost	Old Cumulative 2011	March 2011	Cumulative Total 2011
2	Material	='C:\[February Financials.xls]cost'!D2	100	=B2+C2
3	Personnel	='C:\[February Financials.xls]cost'!D3	200	=B3+C3
4	Overhead	='C:\[February Financials.xls]cost'!D4	300	=B4+C4
5	Total	=SUM(B2:B4)	600	=B5+C6
cost

Cell Formulas
Range	Formula
C5	=SUM(C2:C4)

 

[NikolaB]

To clarify - in the next hypothetical month (April), I create a new spreadsheet from this template and input new values in Column C for the current month. Then I have to change all the references in Column B to [March Financials.xls]. This process is done every month to have the correct cumulative total but I am hoping it can be automated to save time with the sheer number of cells that need to be changed.

Thanks!