I have information held in a separate worksheet and the name of the worksheet
is changing constantly. Within a cell on my current worksheet I have the
file details of where the information I require is to be obtained. I want to
create a formula that takes the file details from the cell above and adds it
to the formula so it knows where to find the information i require

The contents of a cell in my spreadsheet contains a file and worksheet
reference eg
[china garden week 74.xls]Sheet1!$D$15
The contents of this cell is updated by a formula each time the week number
changes. Therefore the number "74" is always changing

I want to enter a formula to return the value in the worksheet cell stated
above without having to amend the formula each time the filename changes.

I cannot use the indirect function as the spreadsheets I wish to link to
will not always be open.