hyper-link from other worksheet

[Darren Douglas (Sprint)]

I have a file with two pages. Page 2 has a hyper-link in
a cell. Page 1 has a formula in a cell that is to pull
the hyper-link to its self from Page 2. The characters
copy over, but not as a hyper-link.
Can you help?

[Mark]

Are you using the HYPERLINK funtion in the formula? If you
use HYPERLINK() and reference the cell in the other
worksheet between the brackets it should work.

[Darren Douglas]

>.
>
Would you provide me with an example. I did not get it to
work. I want to be able to click on the hyperlink on
Page1.

Assume that Page1 has the formula
=HYPERLINK(DATA!AJ2:AJ1372,DATA!B2)
and Page2 has the link that is to be pull over to Page1
(http://netblue.red.com/BackUp Listing.htm)

[Mark]

Maybe I'm misunderstanding your original question. Here's
what I've done.

Worksheet 1 is called Control
Worksheet 2 is called Data

In the Data sheet at cell A1 is the actual hyperlink.

In the Control sheet at cell A1 is the formula =HYPERLINK
(Data!A1)

This brings back the hyperlink address from the Data sheet
and is active.

Sorry for any confusion

[Dave Peterson]

So you don't want a hyperlink to go to another sheet, you want to refer to
another cell and use that cell's hyperlink?

If yes, then I think you'll need a UserDefinedFunction:

Option Explicit
Function getHyperlink(rng As Range) As String

getHyperlink = ""
If rng(1).Hyperlinks.Count > 0 Then
getHyperlink = rng(1).Hyperlinks(1).Address
End If

End Function

Then I could use this formula in another cell:
=HYPERLINK(gethyperlink(sheet2!G8),"displaywhathere")

(Note that this returns the hyperlink address if the hyperlink were added via
Insert|Hyperlink (ctrl-k). It won't return the address if the hyperlink was the
result of the =hyperlink() function.)

If you're new to macros, you can read more about them at David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:
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 (myworkbookname.xls)
right click on it
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Close this window.

Test it out.

--

Dave Peterson
ec3...@msn.com