Using a cell to form part of a filename

Hello,

I have the following formula which references an external worksheet

='H:\Area 14\Natalie\Daily Figures\[DF A14 - 0539.xls]Daily Figures'!Q26

What I would like to is have part of the worksheet filename made up from a value in a cell.

e.g. If cell A1 had the value 0539 I would like that to replace the '0539' in the filename (DF A14 - 0539.xls) with the cell reference so that I can change this value on the fly for a number of similar formulas.

Would this need to be done in code?

Any help would be appreciated as I have tried and tried without success.

Excel97 on Win98.

Many thanks,
Anth

Try...

=INDIRECT("'H:\Area 14\Natalie\Daily Figures\[DF A14 - "&A1&".xls]Daily Figures'!Q26")

Hope this helps!