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.

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.

"Phil" wrote:

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.

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