How to use text value inside a formula

[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.

 

[Jan Karel Pieterse]

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

 

[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?

thanks for any help.

Gary

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

 

[Jan Karel Pieterse]

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