MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Referencing Hyperlinks Using Formulas


Posted by Harley on February 13, 2002 12:40 PM

For example.

2 worksheets.

Worksheet 1 titled "Joe" and Worksheet 2 titled "Mary".

In worksheet "Joe", cell B1, I have created a hyperlink to a webpage using the =HYPERLINK function.

In worksheet "Mary", cell A1, I have the formula =Joe!B1, which effectively copies "Joe cell B1" and places it in "Mary cell A1."

I can get that part to work but I can't get it to copy the actual hyperlink, it's not clickable when it copies into "Mary". Is there a way to get this to work?


Posted by Aladin Akyurek on February 13, 2002 12:57 PM

What follows is a solution by Debra Dalgleish posted in a news group:

QUOTE

You could create two user defined functions, then use them on the
worksheet. Add the following functions to a module in the Visual Basic
Editor:

Function HLText(rng As Range)
'extract visible text from hyperlink
HLText = rng.Hyperlinks(1).TextToDisplay
End Function

Function HLLink(rng As Range)
'extract URL from hyperlink
HLLink = rng.Hyperlinks(1).Address
End Function


Then, use the functions on the worksheet where the hyperlinks are. For example, if there is a hyperlink in cell A3, in cell B3 you could enter:
=HLText(A3)
and in C3:
=HLLink(A3)

UNQUOTE

In your case, you need in A1 in the worksheet named Mary to enter:

=HYPERLINK(HLLink(Joe!B1),HLText(Joe!B1))

==================

Posted by Harley on February 13, 2002 1:08 PM

Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor. Thanks.


Posted by Aladin Akyurek on February 13, 2002 1:22 PM

> Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor.

Open the relevant WB, activate Tools|Macro|Visual Basic Editor, activate Insert|Module, paste both VBA functions in the window with the "(Code)" bit in the title, and activate File|Close and Return to Microsoft Excel.

Now, you're ready to use Debra's UDFs as suggested.

========== Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor. Thanks. : What follows is a solution by Debra Dalgleish posted in a news group


Posted by Harley on February 13, 2002 1:39 PM

I've entered the functions in the module and I've entered the =HYPERLINK formula as you've described in the appropriate cell. Now I'm getting a #VALUE! error, although it is underlined and in blue font. > Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor. Open the relevant WB, activate Tools|Macro|Visual Basic Editor, activate Insert|Module, paste both VBA functions in the window with the "(Code)" bit in the title, and activate File|Close and Return to Microsoft Excel. Now, you're ready to use Debra's UDFs as suggested. ========== : Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor. Thanks.

Posted by Aladin Akyurek on February 13, 2002 3:40 PM

> I've entered the functions in the module and I've entered the =HYPERLINK formula as you've described in the appropriate cell. Now I'm getting a #VALUE! error, although it is underlined and in blue font.

Right. I'm really amazed. It seems these UDF's only work on hyperlinks that you insert thru the Insert|Hyperlink option (on links that reference files on a local machine). Hope the local VBA army will take interest in this issue. You might also consult:

http://www.mvps.org/dmcritchie/excel/buildtoc.htm#URL

for an explanation or better UDFs.

Aladin

======= : > Sounds like it will work, but I've never used Visual Basic Editor. Can you provide me with a walkthrough of how I can get these functions into Visual Basic Editor. : Open the relevant WB, activate Tools|Macro|Visual Basic Editor, activate Insert|Module, paste both VBA functions in the window with the "(Code)" bit in the title, and activate File|Close and Return to Microsoft Excel. : Now, you're ready to use Debra's UDFs as suggested. : ==========