Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Relative Formulae between worksheets
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 |
#2
|
|||
|
|||
Relative Formulae between worksheets
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 "Skid" wrote in message ... |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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change to formulae | Excel Worksheet Functions | |||
Changing a Link Mid-way Across Worksheets | Excel Worksheet Functions | |||
Define list of worksheets | Excel Worksheet Functions | |||
Relative goes absolute between worksheets in data sort! | Excel Discussion (Misc queries) | |||
HELP! How do you--> Lock a set of rows but also link worksheets to | Excel Discussion (Misc queries) |