Hi, I'm using Excel 2007 and have some hyperlinks to other sheets of the workbook in my file. However, they keep breaking depending on whether the workbook is checked out of Sharepoint or not - because if the document IS checked out, then its file path is to my local folder, but if its NOT checked out, the file path is the website. So if you create them using one, then they break when you open it the other way.
I've tried creating the links via the right click > insert hyperlink function, and also using the hyperlink formula wizard.
Any ideas? Is there another way to take me to another sheet of the workbook without using a hyperlink? (or without a macro - our company has very tight security settings and macro enabled workbooks are a right pain for people to open...)
Thanks for any advice!
Related resources
Replies (5)
Please login or register to join the discussion.
I have tried to replicate this on my machine but no matter what I try the hyperlinks work fine.
By default, hyperlinks are stored relative to the location of the current workbook. This can cause problems if you link to another workbook and then change the relative locations of the two workbooks. However, when you are linking to another sheet in the same workbook the relative position of that sheet doesn't change no matter where the workbook is located, so hyperlinks should always work.
It may be that your workbook has been set up so that hyperlinks are always calculated relative to a location other than that of the current workbook. If this is the case then remove this and see if it works.
Click on the Office Button, Prepare, Properties. Choose Advanced properties from the Document Properties dropdown. Click on the Summary tab. Clear the Hyperlink base box if there is anything in it.
If the Hyperlink base box is already empty then I'm afraid I'm out of ideas.
Thanks for the reply
Unfortunately the box you directed me to was already blank so that doesn't look like the problem. Odd that you can't replicate the problem though.
When I hover over the hyperlink to see the address, I get a full internet path name showing as the hyperlink. This is the problem - when the document is checked out on Sharepoint the document is saved on my local drive so although the hyperlink does technically work, it attempts to open the copy of the workbook saved on the server. So what I need the hyperlink to do is refer simply to the other sheet, not the full document location and then the sheet. Maybe it can't be done...
In your excel options check where checked-out files are saved to. Office Button, Excel Options, Save.
In the setting for "Offline editing options..." there are two options: Server drafts location on this computer and The Web Server.
When you open a file from sharepoint it should ask you how you want to open the file (Read only or Check Out and Edit). When you select Check Out and Edit, the Use my Local Drafts folder box should also become available.
If your offline editing options are set so that checked-out files are saved to the server drafts location on this computer then make sure that the Use my Local Drafts folder box is ticked when you Check Out the file. If your offline editing options are set so that checked-out files are saved to The Web Server then make sure the Use my Local drafts folder box is unticked.
Chetan, thanks so much for
Chetan, thanks so much for your help with this. I hate to be obstructive, but this solution isnt really going to solve the problem as many people need to access the document (hence why its on Sharepoint) and so I can't realistically get everyone to change their excel settings :(
I'm just amazed and surprised that the hyperlink insists on using a full file path when its a link internally within the workbook.
Looks like there may not be a solution to this one.
Solved...sort of
Sharon, I managed to replicate your error at my end and this had me worried as we are moving a lot of our data to sharepoint. So I did a bit of Googling and came across someone who had a similar problem here.
I couldn't follow the solution exactly but managed to get the gist of it. I managed to create hyperlinks using the following formula:
=HYPERLINK(CONCATENATE(SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[",""),"#","Sheet2!E15"),"My Link")
This seems to work no matter where the file is stored. When you hover over the hyperlink you will see where the link leads to. You will see that this changes depending on whether the document is checked-out or not.
All you need to do is replace the text in bold with whatever suits your circumstances. Bit of a long-winded solution but you only need to enter the formula once. Thereafter you can copy and edit it as required. Also, if you rename a sheet then you need to remember to edit the hyperlink formula.