Copy date formula to multiple sheets

[Guest]

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.

 

[David Biddulph]

If your formula in sheet 3 is saying sheet1!a1+7, that will still be
11/25/06.

 

[Gord Dibben]

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