following a hyperlink

[Bill]

Maybe I asked this in the wrong groups before, because it's not really a
worksheet function.

I have a summary page and in one column there are hyperlinks to a cell on
another worksheet. In the next column, I would like to use the value of that
hyperlinked cell if a hyperlink exists there. So I need something like
(assuming it's in cell B1)

=SomeFormula + If(IfExistHyperlinkIn(A1), Indirect(a1), 0)

I have tried but can't figure it out. Could someone help?

Also, I see that a hyperlink can be added by right clicking and selecting
from the popup menu or by using the HYPERLINK worksheet function. Any
difference between these methods and any pros or cons?

 

[Dave Peterson]

You can use a UserDefined function to return a true/false if the cell has a
hyperlink that was inserted via Insert|Hyperlink. This UDF won't work with the
=hyperlink() worksheet function.

Option Explicit
Function HasHyperlink(rng As Range) As Boolean
Application.Volatile
HasHyperlink = CBool(rng(1).Hyperlinks.Count > 0)
End Function

Be aware that if you change the hyperlink in the cell, then this formula cell
won't change until your workbook calculates.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=hashyperlink(a1)

And it'll work in the longer formula:
=a2+a3 + If(hashyperlink(A1), Indirect(a1), 0)

If I have a choice, I like to use the =hyperlink() worksheet function.

I can build my strings using other cells.

=hyperlink("file:////" & $a$1 & "/" & a2 & ".jpg")

so if the base address changes, I can just adjust one cell (A1 in my sample) and
all works ok.

This is much more usual when I'm pointing to a file either on a local drive or
on shared server.

I've seen lots of posts where people complain about the hyperlink base changing
when they move workbooks from one location to another. I've never had a problem
with the worksheet function.

If you sort your data, you may find using an extra column of cells and using the
=hyperlink() formula much nicer, too.