I am facing this exact same scenario. Any Ideas?

"RobHan" wrote:

Is there a way to copy a formula to multiple worksheets so that Excel
understands that you don't want to use the first worksheet formula, but the
formula on the preceding worksheet?

Example:

52 sheets (one for each week)
Cell A1 Worksheet 1 = Jan 7 (first week ending date)
Cell A1 Worksheet 2 = =+Sheet1!A1+7, giving Jan 14
If I copy Cell A1 Worksheet 2 to Cell A1 Worksheet 3, I get Jan 14; I want
Jan 21

I want Excel to consider that I'm copying a formula that references a cell
on the previous sheet, so the result should reference the previous sheet, not
the first sheet

Anyone know how to make it work?