Absolute External Link?
 

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 drive:path 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?

--
Regards,
John

Absolute External Link?
 

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")

--
Gary''s Student - gsnu200732


"John Keith" wrote:

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 drive:path 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?

--
Regards,
John