*Relative* file references in a Link?

imacauslan

New Member
Joined
Oct 6, 2006
Messages
3
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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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

New Member
Joined
Oct 6, 2006
Messages
3
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

New Member
Joined
Oct 6, 2006
Messages
3
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

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

Forum statistics

Threads
1,141,587
Messages
5,707,238
Members
421,498
Latest member
matinebi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top