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.