Automate weeks of the year

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
The formula =if(k37=0,'11-9-09'!z137,k137) is in the Sheet Named What?

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

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
Paste This User Defined Function into a Standard Module

Code:
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

Well-known Member
Joined
Jan 22, 2009
Messages
663

ADVERTISEMENT

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

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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

Well-known Member
Joined
Jan 22, 2009
Messages
663

ADVERTISEMENT

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

evaluation = #NAME?
 

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Honestly, no. I don't know why it returns a name? when k31 is 0 right now. Do you have any ideas?
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,750
Messages
5,638,133
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top