Referencing Hyperlinks Using Formulas

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))

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

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.

> 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

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.

> 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. : ==========