How can I create relative HYPERLINKs to local files in a cross platform way?

Question

I want to be able to create a XLSX file which contains links to files in the same directory or in sub-directories and be able to open them on different platforms (macOS, Linux, Windows) and different spreadsheet programs (Excel, LibreOffice, Apple Numbers).

Answer 1

Apple Numbers does not support local HYPERLINKs at all.

Following formula works on macOS, Linux, and Windows and with LibreOffice and Excel:

=HYPERLINK(
  SUBSTITUTE(
    LEFT(
      SUBSTITUTE(CELL("filename"), "\", "/"),
      FIND(
        "?",
        SUBSTITUTE(
          SUBSTITUTE(CELL("filename"), "\", "/"),
          "/",
          "?",
          LEN(SUBSTITUTE(CELL("filename"), "\", "/"))
            - LEN(
                SUBSTITUTE(
                  SUBSTITUTE(CELL("filename"), "\", "/"),
                  "/",
                  ""
                )
              )
        )
      )
    ) & "{{ filename }}",
    "'file://",
    ""
  ),
  "{{ filename }}"
)

Replace {{ filename }} with the file you want to open.

Explanation:

• Replace all \ with / for correct calculations on Windows
• CELL("filename") gets the path of the current spreadsheet file, but slightly differently in Excel and LibreOffice. Therefore the LibreOffice specific 'file:// must be removed.