I've a Excel workbook which has in-cell call's to other Workbook cells (i.e. cells in other different workbooks) e.g. ='C:\Documents and Settings\sid\Local Settings\Temporary Files\Arr\[11.01.11 File-London (FIN).xls]Reporting'!$E$29
The workbooks are monthly reports and so the file names referenced change on a monthly basis. At the moment I'm doing a lot of copying a pasting from one work book to another. This is time consiming and error prone
I wish to automate this process and one thought was to build the File name dynamically based on the month which would be set as a variable. Therefore, 11.01.11 would be inputed into a cell and refernced and next month I'd only have to change this one value and bingo all the links would be updated to pull in the correct data.
However, it seems like Excel doesn't like / permit a variable file name in this type of formula
Can anyone provide a work around or a better solution?
Thanks in advance