Solved

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

Posted on 2016-11-25
7
47 Views
Last Modified: 2016-11-27
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
0
Comment
Question by:WeThotUWasAToad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 22

Expert Comment

by:yo_bee
ID: 41901810
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
 

Author Comment

by:WeThotUWasAToad
ID: 41901824
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
 
LVL 22

Expert Comment

by:yo_bee
ID: 41901827
can you post your Excel file?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 41901830
HI,

pls try

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

Open in new window

Regards
0
 

Author Comment

by:WeThotUWasAToad
ID: 41903214
@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
 

Author Comment

by:WeThotUWasAToad
ID: 41903266
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
 

Author Comment

by:WeThotUWasAToad
ID: 41903494
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

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question