Increment Sheet #

[McAllan]

Hi All.
I have a report which I require every Monday. My Sheet (Tab) name is the Week number of the year. If I Move/Copy this weeks sheet, I always get the same sheet # (2). Is there any way to increment this to Week 14, Week 15 etc.
The main reason for this would be to make sure the date (heading) for the sheet will update automatically. If I try to use cell A1 in Sheet (Week 14) as a relative ref in Sheet 15, it will not update as it appears to use an abs ref.
I dont do well with VBA (yet).
Thanks in advance.
McAllan

 

[texasalynn]

If I try to use cell A1 in Sheet (Week 14) as a relative ref in Sheet 15, it will not update as it appears to use an abs ref.

Can you explain this part? You have two sheets (week 14 & 15) and you want to reference in week 15 items from week 14 sheet?

 

[McAllan]

Yes. In cell A1 of Sheet1, I have a date. In A1 of Sheet2 I have =Sheet1!A1+7. If I try to copy this to Sheet3 & Sheet4 etc etc, =Sheet1!A1+7 is what gets copied through. It seems to me as if you cannot get a relative reference to another Sheet like you can with another cell in the same sheet ($A$1, $A1 or A$1) or am I missing something.
Thanks for your reply.

 

[texasalynn]

so I'm not understanding, but let me ask you like this, you have

Excel Workbook
	A
1	9
Sheet2

Excel 2003

Cell Formulas
Range	Formula
A1	=Sheet1!A1+7

the formula on this sheet(=Sheet1!A1+7) and you want to copy it but have the sheet number increment? So now on the new sheet it would be
=Sheet2!A1+7

have I got it?

 

[McAllan]

Thats it. I am using Excel 2007. I dont want to have to go to each new sheet and manually change it to say =Sheet2!A1 and then =Sheet3!A1 etc.

 

[texasalynn]

ok so this is ugly but should work

=INDIRECT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))-1)
    &RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),1)-1
     &"!A1")

 

[McAllan]

It might look ugly but something sure happens. If I enter it directly, I get a REF# error. If I remove the INDIRECT, I get Week2!A1.
I have got something to start with here. Thanks a million.
It is rather late here 23h45 so I will catch any reply tomorrow. Thanks again.

 

[devthomp]

Yes. In cell A1 of Sheet1, I have a date. In A1 of Sheet2 I have =Sheet1!A1+7. If I try to copy this to Sheet3 & Sheet4 etc etc, =Sheet1!A1+7 is what gets copied through. It seems to me as if you cannot get a relative reference to another Sheet like you can with another cell in the same sheet ($A$1, $A1 or A$1) or am I missing something.
Thanks for your reply.

The following macro will
- add a new worksheet
- set the "A1" value of that worksheet to the last worksheet's date plus 7 days

Sub relativedate()
'
' relativedate Macro
'
Dim i As Integer
Dim d As Date

Sheets.Add After:=Sheets(Sheets.Count)
i = Worksheets(Worksheets.Count).Range("A1").Parent.Index
d = Worksheets(i - 1).Range("A1").Value
Worksheets(Worksheets.Count).Cells(1, 1) = d + 7

End Sub

 

[Haseeb Avarakkan]

May be try something like this;

If your tab names are Week 1, Week 2, week 3......

Define a name PrevWeekValue

referes to:

=INDIRECT("'Week "&SUBSTITUTE(UPPER(REPLACE(CELL("filename",!$A$1),1,FIND("]",CELL("filename",!$A$1)),"")),"WEEK","")-1&"'!A1")+7

If you don't have a space in the sheet name, remove the space after the"'Week ".

Select A1 in the sheets enter

=PrevWeekValue