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]

Here's another idea, with the cell active, in the formula bar (with your cursor), click and highlight (drag fro k to 0) only the portion of the formula: k31=0 Then press the F9 Function key. What happens? Does it change to TRUE?, or something else? Press the Escape Key to restore the original formula; You can do this on any self contained portion of a multiple type formula in order to debug. Hope this helps..

Jim

 

[Zimmerman]

if(TRUE,#NAME?(Z31),k31)

I changed the last part of PrevSheet(k31) to just k31 and it let me evaluate the entire formula. Above is what cam of it.

 

[jim may]

All UDF Functions like this PrevSheet() MUST BE IN a Standard Module, not a Sheet module or the Thisworkbook module. Can you verify Function PrevSheet() is in a Standard module and not a Sheet or Thisworkbook Module?

 

[Zimmerman]

I guess I can't. I put that in the sheet module. I guess i don't know how to put it in a Standard Module. Could you explain how to do that for me?

 

[jim may]

While in the VBE (Alt+F11 from the Spreadsheet) at the Menu, Do Insert, Module. This should create a folder Module1
in the Explorer (which didn't exist before). And your cursor should now be in a Blank Code window. This is the Window to paste the Function. Paste it there and erase/delete the one you previoulsy put in the Sheet module code window.

 

[Zimmerman]

That worked perfect. Thanks so much for everything and all the time you spent with me. Sorry it took so long, but thanks again!!!

 

[Zimmerman]

on a different note,how do you know that much about excel? Where did you learn everything?

 

[jim may]

It has become my life (unfortunately) for the past 3 or 4 years. My wife has become an Excel widow. I work on it all the time; I own 10 or 12 books, ETC...

Jim

 

[Zimmerman]

Have you ever wrote programs for company's or do you just do this on your own. I'm just curious if this is a hobby for you or your job.

 

[jim may]

It has become my job. I am a retired CPA level accountant that prefers to help business with "automation". I've done several sizable turn-key applications that client companies
use DAILY in their processes. I'm always looking for "the next (paying-type) Project".
LOL