*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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

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,176,625
Messages
5,904,087
Members
435,072
Latest member
yijiyap

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