Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

Relative link_location when pasting a =HYPERLINK() formula to a different cell in Excel

Syntax: HYPERLINK(link_location, [friendly_name])

Hello,

Is there a way to copy/paste a cell containing a hyperlink formula so that the new link destination (link_location) is relative rather than absolute?

For example, suppose you have an Excel workbook with the filename TempA.xlsm which contains a worksheet named First. The formula:

=HYPERLINK("[TempA.xlsm]First!B2",B2)

contains two references to Cell B2: one to specify the link destination and the other to define the friendly_name.

User generated imageUser generated image
Because the second B2 reference is relative, copy/pasting this formula to other cells will result in different friendly_name's but retention of its original link_location to Cell B2.

User generated image
I'm wondering if there is a way to make both cell references relative so that pasting the formula to other cells will change the link_location in the same way the friendly_name changes.

Thanks
Avatar of yo_bee
yo_bee
Flag of United States of America image

So you want to always reference B2 for the Link Cell?

If so you need to use
=Hyperlink($B$2,B2)

Open in new window

Avatar of WeThotUWasAToad
WeThotUWasAToad

ASKER

Something else I just noticed is that unlike a link created using the Insert Hyperlink box (Ctrl+k), once a =HYPERLINK() formula (which points to a specific cell) is created, it will not automatically adjust if the link destination position changes at all. This means that if the destination cell is cut/pasted to a different location or if any rows above or columns to the left are inserted/deleted, the hyperlink formula is no longer valid. The friendly_name changes appropriately in these situations (even if its cell reference is absolute), but the cell reference portion of the link_location does not.

I hope I'm just writing the formula incorrectly because the issue I've mentioned here, in my opinion anyway, makes me wonder if the =HYPERLINK() formula is even worth using.
can you post your Excel file?
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@Rgonzo1971

Thank you for that formula. It does exactly what I was after.

I don't completely understand the formula but I will ask my questions about it in a new thread. In the meantime, I've now got what I needed to move forward in this particular project.

Thanks again
One of the things I love about Experts Exchange is that, in addition to getting answers to questions, I'm always learning new things not directly related to my OP. I'm happy to say that just occurred yet again as I was carefully examining your solution formula.

Regarding the =MID() function [Syntax: =MID(text, start_num, num_chars)], I could not understand how the 3rd argument could be greater than: one (for the character found by the 2nd argument) plus the remaining number of characters.

In the past, I've always included a calculation for the 3rd argument which looked something like this:

=MID(G2,G3,LEN(G2)-(G3-1))

But thanks to your formula, I just read in the docs that any value >= 1 + the remaining characters is acceptable for the 3rd argument, hence your use of:

LEN(CELL("filename"))

So I'm posting this additional comment just to let you know that this little tidbit is going to benefit me far more in the long run than the =HYPERLINK() solution you provided. Thus, thank you again.

By the way, whenever this happens, I feel like "AARRGGHH!! How did I not know this?" but I soon calm down by recognizing that at least I know it now. But then and inevitable 2nd question arises: How many/what other similar things are out there which would be just as beneficial but to which I am completely oblivious? In other words:

"I fret about the things I know I don't know but what really worries me are all the things I don't know and don't even know I don't know them."

:)
For future reference, this is the Accepted Solution formula:
=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")))&"!B"&ROW(C2),B2)

Open in new window

And here is a revised more general version:
=HYPERLINK(MID(CELL("filename",B2),FIND("[",CELL("filename",B2)),LEN(CELL("filename",B2)))&"!"&CHAR(COLUMN(B2)+64)&ROW(B2),B2)

Open in new window

The revised version:
• references only a single cell (in this case B2)
• allows for insertion of rows above and/or columns to the left of the hyperlink destination cell without losing its link to that cell
• remains linked to the destination cell even if the latter is Cut/Pasted to a different location

Also, a follow-up thread is located here:
https://www.experts-exchange.com/questions/28985719/Move-an-Excel-HYPERLINK-formula-i-with-relative-cell-references-i-and-or-its-destination-cell-to-a-different-workbook-without-losing-its-function.html