hyperlink changes path on saving in excel

Question

When I save a excel spreadsheet with hyperlinks whilst connected to a server, everything works. If I lose the link to the server and save to my pc, all the hyperlinks changes their paths from the server to my pc. When I reopen the spreadsheet the links no longer work due to the path changing.

I have in options, advance, web options - unticked "update links on save" but it still does it.

Although I use excel 2013, I save in 2003 version as some of my co workers are still using this. 

Thanks for your help

Answer 1

So make sure you have

file:///\r01\Weight Control\monthlyupdate.doc

as text in A1. Then use this in B1:

=HYPERLINK(A1)

Answer 2

Try INDIRECT function. It will preserve your path.

Answer 3

The path originally was -

file:///\r01\Weight Control\monthlyupdate.doc

On saving it changed to -

file:///C:\terry\desktop\Weight Control\monthlyupdate.doc

I'm not sure what you mean by - convert links to text and then use HYPERLINK?

Thanks

Answer 4

The path originally was -

file:///\r01\Weight Control\monthlyupdate.doc

On saving it changed to -

file:///C:\terry\desktop\Weight Control\monthlyupdate.doc

Answer 5

Are these hyperlinks pointing to (otherwise) fixed locations? If so, convert all your links to text and use the HYPERLINK function to create the links.