"Text" formula in one cell becomes "Real&quot

[Walking Shorts]

Let's say that I have a formula in a cell, the formula is something like-

="[jan05.xls]Monthly!D6", so the text that appears in the cell is [jan05.xls]Monthly!D6

Now, my question is, can I cell reference this text in a way that it becomes an actual formula and displays the data from the jan05.xls, without simply just typing the formula into another cell or using the INDIRECT function?

Searched a fair bit on my own, but I can't seem to find anything that quite works besides the INDIRECT function, and it doesn't really suit my needs. Anything?

Thanks,
Wshorts

 

[Norie]

How does INDIRECT not suit your needs?

 

[NBVC]

Why don't you want Indirect?

eg. =Indirect(A1) where A1 contains the string.

 

[Aladin Akyurek]

Re: "Text" formula in one cell becomes "Real&

Let's say that I have a formula in a cell, the formula is something like-

="[jan05.xls]Monthly!D6", so the text that appears in the cell is [jan05.xls]Monthly!D6

Now, my question is, can I cell reference this text in a way that it becomes an actual formula and displays the data from the jan05.xls, without simply just typing the formula into another cell or using the INDIRECT function?

Searched a fair bit on my own, but I can't seem to find anything that quite works besides the INDIRECT function, and it doesn't really suit my needs. Anything?

Thanks,
Wshorts

Are you looking for something like:

=EVAL(B1)

where B1 houses:

="[jan05.xls]Monthly!D6"

Note that EVAL is part of the morefunc.xll add-in. It works here like INDIRECT would and has the same restriction: the file the formula refers to must be in open state. For closed books, you'll need INDIRECT.EXT, also a part of the add-in just mentioned.