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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ker_01 - 07 Mar 2008 15:07 GMT

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

Reply to this Message

Gary''s Student - 07 Mar 2008 15:19 GMT

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

Signature

Gary''s Student - gsnu200772

Reply to this Message

Ker_01 - 07 Mar 2008 15:47 GMT

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

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

Reply to this Message

Gary''s Student - 07 Mar 2008 16:29 GMT

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.

Signature

Gary''s Student - gsnu2007e

> 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
[quoted text clipped - 10 lines]
>
> > =HYPERLINK(INDIRECT("B" & B2)) to make it "clickable"

Reply to this Message

Ker_01 - 07 Mar 2008 20:09 GMT

That worked /perfectly/!

Thank you for the assistance,
Keith

> It sounds like the problem is that the indirect is picking up the
> "friendly
[quoted text clipped - 39 lines]
>>
>> > =HYPERLINK(INDIRECT("B" & B2)) to make it "clickable"

Reply to this Message