Bug in HYPERLINK() in Excel 2003 SpreadsheetML implementation

Question

This isn't really a question, more of an issue I ran across that I hope others can avoid.

If anyone can test in Office 2002, 2007, and 2008/Mac, that may add some important context.

Before replying, please note that Microsoft has released two XML-based formats for spreadsheets: SpreadsheetML and Office Open XML (aka "xlsx"). I've only found this problem in the former. I don't have Office 2007 for Windows or 2008 for Mac to test the newer XML format, but many people still use the SpreadsheetML format for server-generated code because it is easier to generate than the newer format, and more likely to be readable for users with Office 2002 and 2003.

Symptoms / Steps to reproduce

1. Create an XML Spreadsheet file using your own code (not directly in Excel).
2. In the spreadsheet, create a formula that includes a HYPERLINK() function with a dynamically-calculated location.

Example using a lookup function:

<Cell ss:StyleID="s42" 
  ss:Formula="=HYPERLINK(&quot;#'AnotherSheet'!$A$&quot; &amp;
    (MATCH(RC[2],OtherList,0)),&quot;link&quot;)">
<Data ss:Type="String">link</Data></Cell>

1. Open in Excel. Note that as you change the data that the link location is dependent on, the hyperlink destination changes automatically.
2. Save (again, in XML Spreadsheet format, not normal XLS or XLSX).

At this point, you can open the XML in a text editor and note that Excel has added an attribute something like the following to your Cell element:

 ss:HRef="#'AnotherSheet'!$A$20"

1. Open in Excel again. Now, even though the HYPERLINK() formula is the same as before, changes to the dependent data do not change the destination of the link. The hyperlink locations from the previous save are "baked in" due to the attribute mentioned above. Recalculating has no impact, nor does saving, or even re-entering the formula.

Solution

Use "Save As" and convert the Excel file to a standard Excel 97/2000 (XLS/BIFF) format.

Untested

• Is this a problem for SpreadsheetML files saved under Excel 2002 ("XP")?
• Is this a problem for SpreadsheetML files saved under Excel 2008 for Mac?
• Is this a problem for the newer Office Open XML ("XLSX") format in Office 2007 as well?
  • If so, is it also a problem for XLSX in Office 2008 for Mac
  • If so, is it also a problem for XLSX in Office 2003 with the Office 2007 file export plug-in?

Answer 1

No answer required, documenting an issue.