Hi Gary,

Thanks. I've read up on INDIRECT and wrote:

=("["&(INDIRECT("A5"&".xls]Sheet1!D1")))

It gives me a #REF! error. I believe I've written this correctly, but
obviously I haven't. Do I have a syntax problem? What am I not
understanding?


The safest method to create an external link using INDIRECT is:

First hardcode a direct reference.
Then construct a string formula so that the result is identical to that
reference.
Finally wrap that string expression with the INDIRECT function.

Yours is wrong, it should read something like:

=INDIRECT("'["& A5 & ".xls]Sheet1'!D1")

Note that sometimes apostrophs are needed, e.g. when the sheetname or
filename contains a space. I added them just to be sure.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com