Calculated in-document hyperlink

Question

I would like to create a calculated hyperlink that will target a cell within the same document. Insert > Hyperlink allows this option and uses the string #Sheet1!A1, for the Cell A1 on Sheet1, for example.

Is there a way to express this feature as a formula such that it can create the hyperlink based on the result of a calculation?

Answer 1

Yes, there's the HYPERLINK() function:

=HYPERLINK(CONCATENATE("#Sheet1!",T(E2)),"Test")

NB: when working with other language settings then "English (USA)", OOo might require other argument separators (for example: with "German (Germany)", semicolons instead of commas are required).

Split on multiple lines for readability:

=HYPERLINK(
    CONCATENATE(
        "#Sheet1!",
        T(E2)
    ),
    "Test"
)

It will insert a hyperlink, with the target depending on the content of the cell E2. Using the T() function, the Sheet could be set dynamically, too. In my example, E2 could have the value of "A1", resulting in a hyperlink pointing to #Sheet1!A1.

EDIT: Here's a screenshot of a hyperlink with target sheet, target cell and hyperlink text set dynamically:

using this formula:

=HYPERLINK(CONCATENATE("#",T(D1),"!",T(D2)),T(D3))