Calculated in-document hyperlink
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?
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))
精彩评论