I am trying to make a template for a document that will be updated every week, and I was wondering if there was a way to insert some values automatically depending on the value of a cell.
To explain, each file will be named dd-mmm-yy, and I want to reference what was in last week's file and the file from two weeks ago (to quickly track patterns and such). Most of these values are text, if that makes a difference.
Each week's file will be saved into the same folder.
There is an easy way, but it only works if the other workbooks you intend to reference are all open at the same time. It is accomplished using the INDIRECT function.
http://www.contextures.com/xlFunctions05.html#RefWkbk
However, if the other workbooks are going to be closed (or might be closed), then it gets really tricky. You have to start tinkering in VBA, something you may or may not want to do. Some good info on the subject with examples:
You could try a completely alternative approach.
Each time you create a new spreadsheet dd-mmm-yy.xlsx you update hard links week-0.xlsx, week-1.xlsx and week-2.xlsx (and as many more as you need) for the earlier weeks.
Then last week's sheet is always week-1.xlsx and the previous week's week-2.xlsx.
When you create a new spreadsheet, you simply pass its name to a batch file containing:-
del week-2.xlsx
ren week-1.xlsx week-2.xlsx
ren week-0.xlsx week-1.xlsx
mklink /h week-0.xlsx "%1"
If you need to handle a lot more than two previous weeks' sheets, then you can easily use a loop to rename the links. By renaming the links and using week-0.xlsx as the current week, the batch file does not need to work out or deal with the date names of the earlier sheets.
