Date calculation across workbooks / Workbook naming question.

[Phil]

I have a couple of Excel questions that somebody may be able to help me
with.

I have a spreadsheet that requires 52 workbooks (one for each week of
the year) and in one of the cells on each workbook I have the date for
the Saturday of each week.

What I want to do is to have a formula or calculation where if I enter
the first Saturday date of the year in the first workbook, it will
automatically calculate the other 51 throughout the spreadsheet.

Coupled with this I want to be able to make the workbook name the same
as the date cell that changes.

E.g. if I enter 05/02/2008 in cell B1 on workbook 1, I want 12/01/2008
to appear in cell B1 on workbook 2 as well as the workbook name, etc.

I'm trying to avoid having to modify 52 workbooks by adding +7 to the
previous workbooks date. I would like to create two workbooks and then
just copy the second workbook another 50 times.

Any advice would be appreciated.

Regards,

Phil.

 

[Lori]

There is no easy way to refer to the previous sheet but one possibility is to
enter the formulas below as text:

Select all sheets except the first (by holding down shift).
Format cell A1 as text and enter the formula below:

=Sheet1!a1+7*count(choose(1,'*'!A1))

Now choose Edit > Replace "=" with "=" to execute the formulas.

 

[Lori]

Actually, except in xl2002, just entering the formula above across the sheets
should work. To update worksheet names enter in the immediate window
(alt+f11):

for each s in sheets: s.name = s.range("b1"): next s