I have a lot of very large files that pull data from a number of different spreadsheets each month to do analysis on them, and I want to know how to make the references to those spreadsheets dynamic. For instance:
In October I need a value from this chart at this reference:
='C:\Reports\Sales\Oct2010\[MonthlySales.xls]Sheet1'!B9
Each month I need the same value from the same place in a new sheet of the same format:
='C:\Reports\Sales\Nov2010\[MonthlySales.xls]Sheet1'!B9
How do I make this middle part with the date dynamic so all I have to do is update a cell somewhere from Oct2010 to Nov2010?
In October I need a value from this chart at this reference:
='C:\Reports\Sales\Oct2010\[MonthlySales.xls]Sheet1'!B9
Each month I need the same value from the same place in a new sheet of the same format:
='C:\Reports\Sales\Nov2010\[MonthlySales.xls]Sheet1'!B9
How do I make this middle part with the date dynamic so all I have to do is update a cell somewhere from Oct2010 to Nov2010?
