I have set up an Excel sheet in which one of the columns contains a link to
different excel sheet; Rows contain information about individual sampels and
for each row, there's a link to the corresponding analysis worksheet. My
problem is that multiple users access this sheet, and when they save any
changes the links get updated to a path that is not valid for me and others
users. I know how to disable this behavior on my own copy of Excel (under
Tools .. Options .. General .. Web Options) but how can I globally disable
it for the sheet? I've tried UNC paths and drive letter paths, no dice. Is
there a way to lock just the cells that have the links in them? A
file-specific disabling of the update? Any help much appreciated!

You're talking about hyperlinks I guess.

By default, if the destination file of the link is addressable relative
to the folder in which the workbook is saved then Excel will try to
turn the links into relative links. This has the advantage that if
different users access the file by different drive letter mappings or
if you move the files as a set then the links will still work. The
down-side is that if you move the workbook the links will no longer
work.

One solution (before the links get changed to being relative!) is to
set the hyperlink base address for the workbook to a non-existent path
(e.g. \\NoServer\Nodirectory ). If the workbook has such a hyperlink
base address for the workbook then the links will not be changed (as
relative links would be interpreted as starting from the hyperlink base
address).

To set the hyperlink base, in Excel 2003 or earlier (you didn't say
which version, it always helps):

- Open the workbook; ensure the hyperlinks are addressing the correct
locations; File Properties Summary Hyperlink Base : enter some
non-existent path.

An alternative solution is to specify the hyperlinks using the
HYPERLINK worksheet function.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill -
That's a very elegant solution, exactly what I was looking for. I was close
to implementing something horrible, like an onopen macro that would fix the
hyperlinks.Plus I appreciate learning about why and how Excel is doing this.

Thanks for your help!

Dennis

"Bill Manville" wrote in message
...
You're talking about hyperlinks I guess.

By default, if the destination file of the link is addressable relative
to the folder in which the workbook is saved then Excel will try to
turn the links into relative links. This has the advantage that if
different users access the file by different drive letter mappings or
if you move the files as a set then the links will still work. The
down-side is that if you move the workbook the links will no longer
work.

One solution (before the links get changed to being relative!) is to
set the hyperlink base address for the workbook to a non-existent path
(e.g. \\NoServer\Nodirectory ). If the workbook has such a hyperlink
base address for the workbook then the links will not be changed (as
relative links would be interpreted as starting from the hyperlink base
address).

To set the hyperlink base, in Excel 2003 or earlier (you didn't say
which version, it always helps):

- Open the workbook; ensure the hyperlinks are addressing the correct
locations; File Properties Summary Hyperlink Base : enter some
non-existent path.

An alternative solution is to specify the hyperlinks using the
HYPERLINK worksheet function.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup