CODEWITHSUNDEEP
excelhyperlink
RobBaker
RobBaker

Reputation: 305

How do I stop Excel HYPERLINK function breaking?

I have set up Excel calculations with various references that the reader needs to go to (design guidelines, company standards, etc.), included as hyperlinks BUT using the HYPERLINK() Excel function. This decision was taken so that a front sheet (references list) can store each filepath once and then a cell with the HYPERLINK function can be used multiple times, wherever it is relevant/helpful in the calculation; AND also to avoid Excel 'helpfully' mangling the URLs.

The issue I have is that any HYPERLINK cell that is referring to another worksheet to get the link path breaks when someone downloads the Excel file from Sharepoint (the file with the link cell in it, not the URL target). We get an error message saying "Cannot open the specified file.".

Is this an Excel bug (version 2302 build 16130.20810) or are we doing something wrong?

EDIT: If I delete and re-enter the formulae from the affected cells, they are fine, but no amount of forced recalculation achieves this i.e. I have to delete the cell contents and re-add it.

Upvotes: 1

Views: 304

Answers (1)

RobBaker
RobBaker

Reputation: 305

I found the problem, and will post here in case anyone else stumbles into the same issue.

The user building the calculation for me had actually started with 'native' hyperlinks in cells, and I had advised they be replaced by HYPERLINK formulae, for the reasons stated in my question. The mistake was that those native hyperlinks were not removed, just 'overlaid' with the HYPERLINK function.

So when the file was downloaded, the native hyperlinks were breaking (because Excel had mapped them wrongly) and precedence is evidently given to those hyperlinks over the HYPERLINK formula when someone clicks on the cell.

Upvotes: 1

Related Questions