Absolute External Link?

[John Keith]

How can I set a link in the destination workbook to remain pointing to the
same place (the source workbook) no matter where destination sheet has been
copied.

I have a mapped network drive that many other systems rely on having the
same mapped drive letter. I want to utilize this mapping to point via an
external link to lookup data ranges based upon a cycle that is keyed. How do
i make this Link an absolute reference to the location?

The problem I have is that when the destination workbook is copied to a
different folder or drive the link's pointer changes which breaks the link.
I see how to change links from automatic to manual update... but that does
not prevent the driveath to the master sheet from attempting to change to
what excel thinks it should now point to. Is there some way to prefix the
link (Like using "$" in cell references) to force them to be an absolute
reference?

Maybe some code to rebuild the linking cell's formula on open?

Thoughts?

 

[Gary''s Student]

Lets say the source is on G and everyone has G mapped correctly.

=INDIRECT("'G:\[Book1.xls]Sheet1'!$C$20")

will provide protection. Same will work for UNC-type addressing

=INDIRECT("'\\serv1\logytr\:[Book1.xls]Sheet1'!$C$20")