Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy date formula to multiple sheets
I am trying to setup a workbook with 52 sheets (1 per week). I put 11/18/06
in sheet1!a1, then goto sheet2!a1 and enter the formula +sheet1!a1+7. The result is 11/25/06. But when I copy sheet2!a1 to sheet3!a1, the result is still 11/25/06, it should be 12/02/06. -- John |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy date formula to multiple sheets
If your formula in sheet 3 is saying sheet1!a1+7, that will still be
11/25/06. -- David Biddulph "John" wrote in message ... I am trying to setup a workbook with 52 sheets (1 per week). I put 11/18/06 in sheet1!a1, then goto sheet2!a1 and enter the formula +sheet1!a1+7. The result is 11/25/06. But when I copy sheet2!a1 to sheet3!a1, the result is still 11/25/06, it should be 12/02/06. -- John |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy date formula to multiple sheets
John
You can ease your task considerably if you employ a User Defined Function. Function PrevSheet(rg As Range) Application.Volatile n = Application.Caller.Parent.Index If n = 1 Then PrevSheet = CVErr(xlErrRef) ElseIf TypeName(Sheets(n - 1)) = "Chart" Then PrevSheet = CVErr(xlErrNA) Else PrevSheet = Sheets(n - 1).Range(rg.Address).Value End If End Function Copy/paste this to a General Module in your workbook. In sheet1 A1 enter a date. Select sheet2 then SHIFT + Click on sheet52. In A1 of sheet2 enter =PrevSheet(A1) + 7 Select sheet1 again to ungroup the sheets. Gord Dibben MS Excel MVP On Wed, 22 Nov 2006 13:29:01 -0800, John wrote: I am trying to setup a workbook with 52 sheets (1 per week). I put 11/18/06 in sheet1!a1, then goto sheet2!a1 and enter the formula +sheet1!a1+7. The result is 11/25/06. But when I copy sheet2!a1 to sheet3!a1, the result is still 11/25/06, it should be 12/02/06. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum by date rage using multiple sheets | Excel Discussion (Misc queries) | |||
How do I copy a reference formula onto multiple worksheets | Excel Worksheet Functions | |||
How to copy and paste same formula in multiple cells? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
How do I protect formula cells on multiple sheets? | Excel Worksheet Functions |