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.

Fig. 1Fig. 2
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.

Fig. 3
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
WeThotUWasAToadAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
HI,

pls try

=HYPERLINK(MID(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")))&"!B"&ROW(C2),B2)

Open in new window

Regards
0
 
yo_beeDirector of Information TechnologyCommented:
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

0
 
WeThotUWasAToadAuthor Commented:
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.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
yo_beeDirector of Information TechnologyCommented:
can you post your Excel file?
0
 
WeThotUWasAToadAuthor Commented:
@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
0
 
WeThotUWasAToadAuthor Commented:
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."

:)
0
 
WeThotUWasAToadAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.