I have a couple of workbooks our accounting dept enters info into manually once a month. I am trying to eliminate the manual process with macros that input that data from the reports they key off of. In the first workbook I create a new sheet which the accounting department will rename after they run the macro. The second workbook references the newly created sheet, each month. IE, book 1 sheet1 will be renamed to October03, the next month sheet1 will be renamed November03 and so on.
In book2 I can't tell the macro to look at October03 because it needs to look at Noveber03 if it's November,etc. I can't tell it to look at sheet1 of book1 because sheet1 of book1 won't exist until the macro is ran. If I reference sheet1 that doesn't exist the formula is forever messed up, #REF.
I have a layout sheet that has all the formulas in both books. I might just have to do all the formulas in the macro for book2.