indirect referencing cells with hyperlinks brings text not link- any solution?

[Ker_01]

I have a workbook that has 1000+ rows of data. Users need to be able to
reference information quickly using an ID number, so I set up a cell at the
top of the worksheet where they can enter the ID, and using MATCH (in cell
B2) it finds the row number, then I use an INDIRECT to pull the contents of
each column, e.g.

=INDIRECT("A" & B2)
=INDIRECT("B" & B2)
etc.

However, a new column has been added that has hyperlinks to external .doc
and .pdf documents. When I extend the indirect formula over to include the
extra column, it shows the hyperlink text, but is not a hyperlink itself
(e.g. is not clickable).

Is there any way to reference a hyperlinked cell that allows the child
reference to be clickable?

My other option is to add a button and write some VBA to forcibly copy the
link, but I was hoping there is a simpler solution.

Thanks!
Keith

 

[Gary''s Student]

=HYPERLINK(INDIRECT("B" & B2)) to make it "clickable"

 

[Ker_01]

Thank you- I wasn't aware of this option. However, the source cells have
different text and URL properties (due to the length of the URL); for
example, the link might be "14155: Aseptic training" whereas the original
link might be HTTP
://ourcompanyintranet/server/folder/subfolder/subfolder2/filename.doc

Is there any way to use the underlying link? I'm even willing to give up the
text and make the link have generic text, as long as it opens the target
document.

Thanks!!
Keith

 

[Gary''s Student]

It sounds like the problem is that the indirect is picking up the "friendly
name" instead of the underlying URL.

If the actual "clickable" hyperlink is in column B, then in an un-used
column, say column C, enter:

=hyp(B1) and copy down. hyp() is the following User Defined Function:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

Then instead of the indirect function getting the "friendly name", it can
get the URL and feed that to the HYPERLINK() fucntion.

 

[Ker_01]

That worked /perfectly/!

Thank you for the assistance,
Keith