External Links in Excel 2007 with variable source file

[PaHead]

I have read many posts in this discussion group refering to "External Links".
It seems there needs to be an answer for more people than just me.

I have many, many workbooks with intricate cascading linked data.
On each destination workbook/worksheet I want to create many links to source
external workbooks/worksheets/cellref which are not open.

Obviously, when I hard code in the link there is no problem. Whichever way I
try to create a formula that can link depending upon other values in the
worksheet then I have the problem that the source workbook is not open. This
includes the INDIRECT function solutions mentioned elsewhere.

Is there a way to accomplish an external link where the link is not hard
coded? If there is then using ordinary worksheet functions would be best.
Otherwise, is there a VB method that can be coded to work?

I don't want to open a destination workbook and see many other source
workbooks opening and closing while gathering the required link data. I would
like it to be transparent just as a hard coded link is.

 

[Ron Rosenfeld]

I have read many posts in this discussion group refering to "External Links".
It seems there needs to be an answer for more people than just me.

I have many, many workbooks with intricate cascading linked data.
On each destination workbook/worksheet I want to create many links to source
external workbooks/worksheets/cellref which are not open.

Obviously, when I hard code in the link there is no problem. Whichever way I
try to create a formula that can link depending upon other values in the
worksheet then I have the problem that the source workbook is not open. This
includes the INDIRECT function solutions mentioned elsewhere.

Is there a way to accomplish an external link where the link is not hard
coded? If there is then using ordinary worksheet functions would be best.
Otherwise, is there a VB method that can be coded to work?

I don't want to open a destination workbook and see many other source
workbooks opening and closing while gathering the required link data. I would
like it to be transparent just as a hard coded link is.

Perhaps the INDIRECT.EXT function from Longre's free morefunc.xll add-in might
be helpful. http://xcell05.free.fr/morefunc/english/index.htm

It allows referencing a closed workbook with an INDIRECT-like function.
--ron

 

[PaHead]

Dear Ron,

Thank you very much for your reply.

It has solved my problem completely with the extra use of SETV and GETV.

Your answer was invaluable.

I can post an example of what I have done if anyone wants it.

Again, many thanks.

 

[Ron Rosenfeld]

Dear Ron,

Thank you very much for your reply.

It has solved my problem completely with the extra use of SETV and GETV.

Your answer was invaluable.

I can post an example of what I have done if anyone wants it.

Again, many thanks.

I'm glad that helped.

I find morefunc to be a very useful add-in, in a number of areas, so long as
you remain aware of its limitations (mostly size issues due to it being an
XLL).
--ron

 

[JGT]

PaHead, can you please post an example or send me a sample file?

 

[JGT]

Anybody have a workable link to download this add-in?
Any contact with the author?