*Relative* file references in a Link?

[imacauslan]

I'm having a real problem that I can't quite figure out. I am hoping someone can help me.

I have my "Main" Workbook open, and in it I need to reference a cell value in one -- out of 100 or so -- other Workbooks, each of which has data on a given stock I occasionally follow (e.g., 'IBM.xls', 'MSFT.xls', etc.).

Now to display the value in cell B4, I know can write:


[cell B4:] ='C:\MyStocksToFollow\[IBM.xls]Sheet1'!B4 -- and this will bring in the value from my "IBM.xls" file...

But what I'd really like to do is write the link statement above, not as an absolute reference (to IBM.xls), but with a relative reference to some some other cell's content which I might change at any time (by typing a new stock's name into cell A1, let's say).

So, in "pseudo-code", I'd like to do something like

[cell A1:] "IBM"

[cell B4:] ='C:\MyStocksToFollow\[' + A1 + '.xls]Sheet1'!B4


How can this be accomplished? I'd really appreciate any help! Thanks.

 

[hatman]

Welcome to the board!

Sounds like you need the Indirect() function.

=indirect(char(39)&"C:\MyStocksToFollow\["&A1&".xls]Sheet1"&char(39)&"!B4")

or, if you know you won't have spaces in the file/pathname: =indirect("C:\MyStocksToFollow\["&A1&".xls]Sheet1!B4")

 

[imacauslan]

Thanks, hatman!

I found the INDIRECT function, but according Excel's online Help, it says the workbook that you're trying to refer to has to be OPEN also...else it generates an error.

If so, that would defeat my purpose, since I have about 200 workbooks, and loading them all in memory at once...I don't even know if its possible (probably certainly not advised!).

Any other idea how I might approach this? I'd certainly appreciate it if so...

 

[imacauslan]

Follow-up: "Link"ing to a Workbook w/a *Relative*

Thanks, hatman!

I found the INDIRECT function, but according Excel's online Help, it says the workbook that you're trying to refer to has to be OPEN also...else it generates an error.

If so, that would defeat my purpose, since I have about 200 workbooks, and loading them all in memory at once...I don't even know if its possible (probably certainly not advised!).

Any other idea how I might approach this? I'd certainly appreciate it if so...

 

[hatman]

The Morefunc Addin by Laurent Longre has a function called indirect.ext() which behaves EXACTLY like the native indirect() function EXCEPT that it will update links with closed workbooks... http://xcell05.free.fr/ Other than that, I can't think of anything helpful that is not non-trivial.

 

[imacauslan]

hatman,
looks great! I will check it out. Thanks again