I have weekly financial reports I update each week. Across multiple spreadsheets, there are cells that need the same information. I only want to enter the data once and have the other spread sheets reference the cell by using code similar to this:
='[Internet AE Report Santa Rosa 6-17-11.xls]2011'!$M$60
Each week the "6-17-11" portion of the file name changes to reflect the new date. When I rename the file, the formula changes each week on ALL spreadsheets. This is where the problem lies.
Let me explain it better by showing the following scenario:
I have a file named InternetWrapUp-6-10-11.xls that pulls from a file named InternetAEReportSantaRosa 6-10-11.xls.
This week, I opened both of those files made the appropriate input changes and renamed the files to reflect the new date, each ending with "6-17-11.xls"
Next week I'll open the 6-17-11 files and repeat the process ending both files with "6-24-11.xls" When the file name is changed, it automatically changes this referencing code:
='[Internet AE Report Santa Rosa 6-17-11.xls]2011'!$M$60
to reflect the new file name... and seems to affect all of my older files. This means I can't go back and look at a file from April or any other previous date and see accurate information because the the referencing code has changed.
It would save me about 30 minutes each week if I didn't have to re-enter data each week across multiple spread sheets, I'd like to just enter it once and have the other sheets pull from that, but renaming the files causes the problem I mentioned.
How do I solve this? Does it have to do with when files are opened at the same time?
Thanks
='[Internet AE Report Santa Rosa 6-17-11.xls]2011'!$M$60
Each week the "6-17-11" portion of the file name changes to reflect the new date. When I rename the file, the formula changes each week on ALL spreadsheets. This is where the problem lies.
Let me explain it better by showing the following scenario:
I have a file named InternetWrapUp-6-10-11.xls that pulls from a file named InternetAEReportSantaRosa 6-10-11.xls.
This week, I opened both of those files made the appropriate input changes and renamed the files to reflect the new date, each ending with "6-17-11.xls"
Next week I'll open the 6-17-11 files and repeat the process ending both files with "6-24-11.xls" When the file name is changed, it automatically changes this referencing code:
='[Internet AE Report Santa Rosa 6-17-11.xls]2011'!$M$60
to reflect the new file name... and seems to affect all of my older files. This means I can't go back and look at a file from April or any other previous date and see accurate information because the the referencing code has changed.
It would save me about 30 minutes each week if I didn't have to re-enter data each week across multiple spread sheets, I'd like to just enter it once and have the other sheets pull from that, but renaming the files causes the problem I mentioned.
How do I solve this? Does it have to do with when files are opened at the same time?
Thanks
