Is there a way to cell value in a formula that leads to a different file path?

[albardit18]

Hi,

I am working on a template that has formulas that calculate values from other worksheets and am wondering if there is an easier way to do what I am trying to do.


Basically this is a weekly report that has formulas that calculate numbers from spreadsheets from the week before.

The formulas contain file names like the example below:

'C:\Project\Reports[Report 10.01.12]Sheet 1'!A:A...


Each file name is the same "Report" and a date after it. So, 10.01.12 ; 10.08.12 ; etc..


Is there a way to make the file path part of a formula so that it looks at a cell within the spreadsheet to pick up the date?
Say cell A1 has the date 10.01.12 is there a way to define the file path by looking up part of the path by a cell in the active spreadsheet?

I am not sure if its possible or not but any suggestions are appreciated.

[alansidman]

Look at using the indirect function to notify the formula to look at a cell in a spreadsheet for the date part of the forumula.

Look at this.

http://www.techonthenet.com/excel/formulas/indirect.php