monthly budget

[Andy G]

I am using the excel template for a monthly budget. I have copied the sheet
to make many sheets (12 for each month) in one workbook. I want to
reference the months previous "Actual Cost" to use on the current month's
"Projected Cost". I have to do that manually every time I copy to a new
month. Is there a way to tell Excel to reference the right month? So if I
copy the July sheet into and August sheet the Projected cells will reference
July's actual costs and not Junes actual costs.

One more thing...Can I set up excel so that if I make a field change in one
sheet it will make the change in all sheets in the workbook. Ie. Changing
the label in a cell in one sheet would change that same label in every
sheet.

Thanks for the help!

 

[Debra Dalgleish]

Setup the Jan sheet
Copy the Jan sheet, and name it Feb
In cell A2, enter the formula:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
In cell A4, enter the formula:
=CHOOSE(MONTH(DATEVALUE(A2&" 1,
"&YEAR(TODAY()))-1),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov")
You can format this cell with white font, to hide the month name

In cell B8 (the first projected cost), enter:
=INDIRECT($A$4&"!"&ADDRESS(ROW(),COLUMN()+1))
Copy the formula down to the remaining projected costs in column B

 

[Dave Peterson]

The easy one first.

If you want to change A1 in a bunch of worksheets, just group those worksheets
first.

Select the first tab, and ctrl-click on subsequent tabs. Watch the title
bar--it'll have [Group] in it. That means anything you do to one of those
grouped sheets, you do to all of them.

And you'll want to ungroup them when you're done. Just rightclick on a
worksheet and select Ungroup sheets.

I think I'd change my formulas slightly.

Instead of things like

=if(july!c9="","",july!c9)

I'd put the month name in an unused cell (say A1).

Then use this kind of formula:

=IF(INDIRECT("'" &$A$1& "'!c9")="","",INDIRECT("'" &$A$1& "'!c9"))

Now copy your sheet and just change the value in A1 to the correct sheet name.

If you enter an incorrect worksheet name, you'll get #ref! errors.

 

[bigwheel]

Hi Andy G

I am using the excel template for a monthly budget. I have copied the sheet
to make many sheets (12 for each month) in one workbook. I want to
reference the months previous "Actual Cost" to use on the current month's
"Projected Cost". I have to do that manually every time I copy to a new
month. Is there a way to tell Excel to reference the right month? So if I
copy the July sheet into and August sheet the Projected cells will reference
July's actual costs and not Junes actual costs.

Do you mean that if a cell in your January worksheet contains "Actual Cost"
you want to show that as "Projected Cost" in the February worksheet?
If so, the February cell should contain something like
=JANUARY!<cell-reference>

One more thing...Can I set up excel so that if I make a field change in one
sheet it will make the change in all sheets in the workbook. Ie. Changing
the label in a cell in one sheet would change that same label in every
sheet.

Do you mean labels, as in the "Actual Cost" from the above, or comments
within the cells?
If the former, the formula is the same as it was for the values.