Solved

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

Posted on 2016-11-25
7
50 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 23

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 23

Expert Comment

by:yo_bee
ID: 41901827
can you post your Excel file?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 51

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 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