Copy worksheet & maintain cell reference across worksheets

I have a workbook with a worksheet set up initially with a sheet named
1-01-06 and another sheet named 12-31-06, these refer to the first & last
weeks of 2006.
Both worksheets have a cell with the following formula
=SUM('1-01-06:12-31-06'!B12:F12) which sums the total of b12 thru f12 across
both worksheets.
I want to copy the first worksheet 24 times, renaming each copy to the date
for sunday of each week.

When I copy the worksheet to a new worksheet that is located between 1-01-06
& 12-31-06 the formula for the above cell changes to =SUM(#REF!B12:F12). I
have tried putting a $ sign in various places in the original formula to make
it absolute with no success, when I do this I get this result
=SUM('$1-01-06:[$12-31-06]$12-31-06'!B12:F12) by entering it this way
=SUM('$1-01-06:$12-31-06'!B12:F12) which gives me a #REF! value in cell.

How do I enter thus formula to make it copy exactly the same when I copy the
worksheet.?

Thanks
Gary D.

SOme thing else which would be nice to do when copying the worksheet is to be
able to have the formula copy as follows.

=SUM('1-01-06:12-31-06'!B12:F12) original formula

=SUM('1-01-06name of worksheet)'!B12:F12) copied worksheet formula

Thanks
Gary D.

"dingy101" wrote:

> I have a workbook with a worksheet set up initially with a sheet named
> 1-01-06 and another sheet named 12-31-06, these refer to the first & last
> weeks of 2006.
> Both worksheets have a cell with the following formula
> =SUM('1-01-06:12-31-06'!B12:F12) which sums the total of b12 thru f12 across
> both worksheets.
> I want to copy the first worksheet 24 times, renaming each copy to the date
> for sunday of each week.
>
> When I copy the worksheet to a new worksheet that is located between 1-01-06
> & 12-31-06 the formula for the above cell changes to =SUM(#REF!B12:F12). I
> have tried putting a $ sign in various places in the original formula to make
> it absolute with no success, when I do this I get this result
> =SUM('$1-01-06:[$12-31-06]$12-31-06'!B12:F12) by entering it this way
> =SUM('$1-01-06:$12-31-06'!B12:F12) which gives me a #REF! value in cell.
>
> How do I enter thus formula to make it copy exactly the same when I copy the
> worksheet.?
>
> Thanks
> Gary D.
>

Hi!

Kind of a clunky way to do it:

Convert the formula to a text string by preceding it with an apostrophe:

'=SUM('1-01-06:12-31-06'!B12:F12)

Make your sheet copies

Then group all those sheets together, select the cell with the formula in it
and edit out the apostrophe. Then make sure you ungroup the sheets.

Biff

"dingy101" <[email protected]> wrote in message
news:[email protected]...
>I have a workbook with a worksheet set up initially with a sheet named
> 1-01-06 and another sheet named 12-31-06, these refer to the first & last
> weeks of 2006.
> Both worksheets have a cell with the following formula
> =SUM('1-01-06:12-31-06'!B12:F12) which sums the total of b12 thru f12
> across
> both worksheets.
> I want to copy the first worksheet 24 times, renaming each copy to the
> date
> for sunday of each week.
>
> When I copy the worksheet to a new worksheet that is located between
> 1-01-06
> & 12-31-06 the formula for the above cell changes to =SUM(#REF!B12:F12).
> I
> have tried putting a $ sign in various places in the original formula to
> make
> it absolute with no success, when I do this I get this result
> =SUM('$1-01-06:[$12-31-06]$12-31-06'!B12:F12) by entering it this way
> =SUM('$1-01-06:$12-31-06'!B12:F12) which gives me a #REF! value in cell.
>
> How do I enter thus formula to make it copy exactly the same when I copy
> the
> worksheet.?
>
> Thanks
> Gary D.
>

Hi

One way might be to create 2 dummy sheets titled First and Last.
Drag these to a position that wraps your other sheets.
Make the formula = SUM(First:last!B12:F12)


--
Regards

Roger Govier



dingy101 <[email protected]> wrote:
> I have a workbook with a worksheet set up initially with a sheet named
> 1-01-06 and another sheet named 12-31-06, these refer to the first &
> last weeks of 2006.
> Both worksheets have a cell with the following formula
> =SUM('1-01-06:12-31-06'!B12:F12) which sums the total of b12 thru
> f12 across both worksheets.
> I want to copy the first worksheet 24 times, renaming each copy to
> the date for sunday of each week.
>
> When I copy the worksheet to a new worksheet that is located between
> 1-01-06 & 12-31-06 the formula for the above cell changes to
> =SUM(#REF!B12:F12). I have tried putting a $ sign in various places
> in the original formula to make it absolute with no success, when I
> do this I get this result
> =SUM('$1-01-06:[$12-31-06]$12-31-06'!B12:F12) by entering it this
> way =SUM('$1-01-06:$12-31-06'!B12:F12) which gives me a #REF! value
> in cell.
>
> How do I enter thus formula to make it copy exactly the same when I
> copy the worksheet.?
>
> Thanks
> Gary D.