Solved

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

Posted on 2016-11-25
7
29 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
  • 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

930 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now