Thread: Indirect to refer to a different workbook?

I have the following formula:

='C:\TEST\Reports\2010\[Test Ratios.xlsx]Sheet1'!Y7

Is there a way that I can write this same formula, but for the year, have it refer to a particular cell that contains a year?
For example, let's say that cell A1 contains the year (in this case, "2010"). Is there a way to get the desired result but referece the year portion dynamically by referring to cell A1.

One idea was to use INDIRECT.

=INDIRECT("'C:\TEST\Reports\" & A1 & "\[Test Ratios.xlsx]Sheet1'!Y7"

This doesn't seem to work. Additionally, I need to keep Sheet1'!Y7 out of the quotes so that the cell reference will adjust as it is copied and pasted to other cells.

Any ideas?

Thanks,
Paul