How to keep Hyperlinks active within spreadsheet

[JG2023Help]

I have created hyperlink formulas in excel to refer to another tab in same spreadsheet for additional data entry. Below is an example of the formula:

=IF(I26=Data!$M$3,HYPERLINK("#'Linked Notes'!A"&N26,Tasks!H26&" Notes")," ")

The cell with the hyperlink is then copied using =J26 and organized with associated data using INDEX and similar functions. I want the hyperlink in J26 to remain active at the end of the organization, so I can quickly access the 'Linked Notes' tab. However, as soon as I use =J26, the hyperlink is not returned, just the friendly text. Can you recommend a trick to keeping the hyperlink active?

[#2]

@JG2023Help A direct cell reference "just" gets the value from that cell, not other things like a hyperlink. To get a (dynamic) hyperlink, you must have the HYPERLINK function in that cell.

[#3 · Accepted solution]

Hi @JG2023Help,

When you use a formula like `=J26` to replicate the content of a cell to another location in Excel, you are copying the computed value or outcome of the formula, not the actual hyperlink itself.

You can try to use this method:

Using a helper column

1. Create a helper column to store the hyperlink formula. This can be any unused column in your spreadsheet.
2. In the helper column, enter the hyperlink formula for the cell that contains the hyperlink you want to copy. For example, if the cell with the hyperlink is in cell J26, you would enter the following formula in the helper column:

=HYPERLINK("#'Linked Notes'!A"&N26,Tasks!H26&" Notes")​

1. Copy the formula from the helper column to the cell where you want the hyperlink to appear.
2. Delete the helper column.

Please click Mark as Best Response & Like if my post helped you to solve your issue.
This will help others to find the correct solution easily. It also closes the item.

If the post was useful in other ways, please consider giving it Like.

Kindest regards,

Leon Pavesic
(LinkedIn)

[#4]

I like the helper formula idea. I created a helper formula in the column that the Hyperlink formula sends you to which returns the friendly name. Once data is organized I will try to use lookup formula to find the friendly name and put inside a lookup formula.