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-06:(name 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" wrote in message
...
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 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.