Excel 2003 - Linking Formulas, Worksheet to Worksheet

[Guest]

I need to set up a workbook with one worksheet per month for twelve months.
There will be formulas in each worksheets, two of which need to carry forward
totals from the previous month's worksheet. How do I link worksheets in this
way? How do I replicate that resulting formula on ALL the worksheets without
entering the formula in every single worksheet?

 

[Dave O]

Let's say it's February and you need to link to the January totals.
Assume also that the tabs in your workbook are called Jan Feb Mar etc.
Click on the Feb tab, and land the cell pointer on the cell that hold
the January data. Start a formula by pressing the = sign, then click
on the Jan tab and click the cell with the carryforward total. Press
Enter, and the Feb tab will appear again, and reflect the January
number.

Since tab names change with month names, replicating the resultant
formulas could be accomplished with a macro. But since the entire
operation takes about 3 seconds the amount of time spent automating it
wouldn't generate a time savings.

 

[Guest]

I know how to do what you state below. I didn't realize that this would
replicate the same formula to the OTHER worksheets, though. i.e. pick up
total from Jan for Feb worksheet, pick up Feb total for Mar worksheet, and so
on.

thanks.

 

[Guest]

Okay, I did what you said and the formula worked when I ref the Jan cell in
the Feb worksheet. However, when I went to copy the Feb worksheet to use it
as the Mar worksheet, the formula did not change itself to read the same cell
in the Feb worksheet (for entry on the Mar worksheet). I think I am still
doing something wrong.

 

[Dave O]

Tab references don't shift in the manner you're expecting,
unfortunately. This would still be a manual change from month to
month. However, it doesn't take a lot of time, as you found out, and
you could set up all 12 months at one sitting- then copy the file to be
used next year and subsequent years, and the work would be done once
and for all.