Copy worksheet with formula referencing prior worksheet

[Darnell S]

Hi MrExcel,

I need help in copying a worksheet formula
from one worksheet to another but have it reference the prior worksheet... ex.

Aug02 worksheet ending balance for cell "C10" is $100. I then use "Move or Copy" function to create a copy of the current worksheet and rename it Sept02.

Sept02 beginning balance would be
(=+'Aug02'!C10) display a balance of $100.(correct). Now Sept02 ending balance is $500. I then use the "Move or Copy" function to create a copy of the current worksheet (Sept02) and rename it Oct02.

Now here's my problem.... the Oct02 beginning balance is referencing (=+'Aug02'!C10)August ending balance of $100 instead of
September ending balance of $500.

Help... How to I setup up my worksheet correctly.

Darnell

 

[Anne Troy]

Instead of moving and copying your monthly sheets, you should have ONE sheet---maybe called "template"---that you make a new copy of each month.

You should then have no need to rename your sheets, etc.

Or have I missed your point?

 

[Darnell S]

Ann,

I beleive you are missing my point. I need to ulitize the data on the current worksheet
month to month, I just need to know how to have a cell formula update itself when I create a copy for the next month... ex.

I need the formula (=+'Aug02'!C10) on September worksheet to become (=+Sept02'!C10) on the October worksheet.

My goal is the have the ending balance on one month worksheet to become the begining balance on the next month worksheet.