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?

thanks for any help.

Gary

Jan Karel Pieterse wrote in message ...
Hi Gary,

I want to grab the text contents of a cell and use that in a formula
in another cell.

So, say, for example, A1 contains TEST. Can I then put TEST in a
formula automatically in another cell formula, so that the formula
will essentially be:
=VLOOKUP(A5,[TEST.xls]Sheet1!$A4:$H203,2,FALSE)...

I have stock tickers in a cell and filenames named after each ticker
and want to do lookups into each ticker's file. The ticker symbols
change every day. Any advice appreciated.


You want to take a look at the INDIRECT worksheet function, which does
what you want, with one annoying restriction: the files pointed to must
be open in Excel for this to work.

Regards,

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