Automate weeks of the year

[Zimmerman]

I have an excel sheet that has a tab for every week of the year. Each week refers back to the last week unless other information has been added into a cell. I have to copy the previous week everytime and change the formulas to read the previous week. is there a way to have it do this for me? Meaning it might say if(k37=0,'11-9-09'!z137,k137). when i add the next week i'll have to change this to say 11-16-09 instead of 11-9-09. I have about 150 of these in each tab. I just want to automate it better so I don't have to keep doing this. Any suggestions

Excel 2003
Windows XP

 

[jim may]

The formula =if(k37=0,'11-9-09'!z137,k137) is in the Sheet Named What?

'11-9-09' or '11-16-09'

 

[Zimmerman]

11-16-09. What it's doing is saying if there's anything in a cell that's adding up other cells in sheet 11-16-09, then use that number. If not refer back to the cell in 11-9-09

 

[jim may]

Paste This User Defined Function into a Standard Module

Function PrevSheet(rg As Range)
    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

Then use this formula: =IF(K37=0,PrevSheet(Z137),PrevSheet(K137))

See if that works for you -- Try all this on a Back uP of your file, Not the Original...

 

[Zimmerman]

It just returned "NAME?" did i do something wrong?

 

[jim may]

With the formula cell selected and Active, click on Tools, Formula Auditing, and Evaluate Formula - This will bring up Step through Capability.. As you Click Evaluate watch the results, this may flag something that is going wrong.. Write back with results

 

[Zimmerman]

if(k31=0,PrevSheet(Z31),PrevSheet(K31)

evaluation = #NAME?

 

[jim may]

So? Any Ideas as to why?

 

[Zimmerman]

Honestly, no. I don't know why it returns a name? when k31 is 0 right now. Do you have any ideas?

 

[jim may]

Did you Click the Evalute Button from start to finish? As you did, did any portion of the formula produce an error (like #Name?)? If so Post only that portion