How to use text value inside a formula
 

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.

How to use text value inside a formula
 

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

How to use text value inside a formula
 

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

How to use text value inside a formula
 

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