Carrying sequential weekly date formula across worksheets

Hi

I am writing a roster and want to carry dates forward on a weekly basis so that each time a new worksheet is created - presumably by copying the previous sheet - the formula for calculating the end date of the weekly roster will carry forward to each subsequent sheet.

I have daily dates set to automatically calculate and are dependent of a fixed date to calculate from. This is a simple formula (day 1 date = day 7 date -6, day 2 date = day 7 date - 5 etc) This formula exists solely within one worksheet so when the sheet is copied and the correct roster period end date is set the other days automatically populate.

I had set a formula between two worksheets where I put in a formula for the second sheet to calculate the end date for the roster being the end date on the previous sheet + 7 which worked, however when I copied this worksheet as a third worksheet, including the date calculation formula, the date remained as for the second sheet because it was using a formula that was linked to a cell in the first worksheet.

Is it possible to copy subsequent worksheets with a date calculation formula that makes the job of weekly rostering a bit easier.

thanks

Mark

If your sheets end with a number then assuming your worksheets were just called Sheet1,2,3,etc.
In A1:="Sheet"&IF(--RIGHT(CELL("filename",B1),2)<11,"0","")&--RIGHT(CELL("filename",B1),2)-1
in A2: =INDIRECT(A1&"!A2")+7

A2 is where the date was originally stored....probably sheet 1.

Note:- Sheets must end with 2 digits.... e.g. Sheet01, Sheet02, Sheet03, Sheet04, etc. - Sheet99.