Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy worksheet & maintain cell reference across worksheets
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy worksheet & maintain cell reference across worksheets
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Copy worksheet & maintain cell reference across worksheets
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
Copy from worksheet to another x times | Excel Discussion (Misc queries) | |||
Using a cell reference to refernce worksheet in another work book | Excel Worksheet Functions | |||
GET.CELL | Excel Worksheet Functions |