Relative Formulae between worksheets

[Guest]

I have a series of worksheets within a workbook and want to advance a date by
7 on each work sheet. The date is in Cell A1 in worksheet 1 and on worksheet
2 I have the formula ='Sheet1'!A1+7. When I copy and past this into worksheet
3 it does not change the formula to ='Sheet2'!A1+7 but retains the origianl
sheet reference. Is there any way of achieving what I want or am I going to
have to change the fromula on each worksheet manually? I am currently using
Excell 2003.

 

[Jim Rech]

References between worksheets are always absolute in Excel so you'll have to
edit the formula. You could use a macro to make entering the date formula
simpler though. For instance, select A1 on the new sheet and run this:

Sub EnterDate()
Dim ShtIdx As Integer
ShtIdx = ActiveSheet.Index
If ShtIdx > 1 Then
With ActiveCell
.Formula = "=" & Worksheets(ShtIdx - 1).Range(.Address) _
.Address(False, False, , True) & "+7"
End With
End If
End Sub


--
Jim
|I have a series of worksheets within a workbook and want to advance a date
by
| 7 on each work sheet. The date is in Cell A1 in worksheet 1 and on
worksheet
| 2 I have the formula ='Sheet1'!A1+7. When I copy and past this into
worksheet
| 3 it does not change the formula to ='Sheet2'!A1+7 but retains the
origianl
| sheet reference. Is there any way of achieving what I want or am I going
to
| have to change the fromula on each worksheet manually? I am currently
using
| Excell 2003.
| --
| Skid