Archive for March, 2016

Fixing HYPERLINK functions in Excel and OpenDocument Exports

March 24th, 2016 No comments

Update: as of version 2.10.13, HYPERLINK functions should work in downloaded Excel .xlxs files automatically.

If you have a form that includes uploaded files, then .xlsx and .ods export files will have HYPERLINK functions for each file entry. This is intended to enable you to download the files.

But the problem is that the export code doesn’t support creating functions. So the literal text of the function appears in the cell, but Excel, OpenOffice & LibreOffice don’t know that it is a formula.

The fix for this is:

  1. Open the export file
  2. Select the cells with formula text
  3. Do a “Find/Replace All” operation: find “=” and replace with “=”

That doesn’t change the formula but tricks Excel, OpenOffice & LibreOffice into re-evaluating the cells at which point it converts them to an actual formula.

In Excel selecting a single cell and pressing F2-Enter will convert the cell to a formula. But this only works on one cell, not a range of selected cells.

Categories: export, tips Tags: