David,

Thanks for the background info on hyperlinks - Excel is so
big!

Yes, I would like to include the offset funtion in my
hyperlink formula (I didn't know this was possible). I've
had a go at it but can't work it out. What does the full
formula look like now?

Regards

Tony
-----Original Message-----
Hi Tony,
In the VBA Help for "FollowHyperlink Event"

Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)
Target Required Hyperlink. A Hyperlink object that
represents the destination of the hyperlink.

and for "Hyperlink Object"
Represents a hyperlink. The Hyperlink object is a member
of the Hyperlinks collection.

I believe Hyperlink object only applies to the kind
builtin hyperlink
that you create by right a click or that you obtain from
pasting from
HTML and not to worksheet formula hyperlinks. That is
the way they
have been used in the newsgroups and the object type of
hyperlinks
have problems with memory when you have a bunch of them
in Excel 95.
The object hyperlinks are not a problem with Excel 2000
and up as
long as you have Windows 2000 and up.

Perhaps a Selection Change Event would work. If it
really doesn't
matter how you got there you could check where you were,
but actually
from your example wouldn't you just hyperlink to
either the tenth row
or include OFFSET(cell,10,0) in your HYPERLINK
Worksheet Formula.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Tony" wrote in
message ...
David & Frank,

Thanks lads - the new hyperlink formula =HYPERLINK
("#'Teams'!" & ADDRESS(MATCH(C3,Teams!
$A$1:$A$10000,0),1),C3) now jumps to the correct row on
the correct worksheet. I am using Excel 2000
However, Excel does not seem to treat the new formula
as a
real hyperlink. I am capturing the event after a
hyperlink has been clicked to so that it runs a macro.
Strange thing though - nothing works - any suggestions?.
Here is the code to capture the event (on same worksheet
(League) as new hyperlink formula).

Private Sub Worksheet_FollowHyperlink(ByVal Target As
Hyperlink)
Macro3
End Sub

Sub Macro3()

ActiveSheet.Outline.ShowLevels RowLevels:=2
ActiveCell.Offset(10, 0).Select 'move down 10 rows to
show
full team
End Sub

-----Original Message-----
Hi David
thanks for this :-)

--
Regards
Frank Kabel
Frankfurt, Germany


David McRitchie wrote:
That would probably work on 2002 but I don't think it
would
work on Excel 2000.

The following will not work on Excel 2000
=HYPERLINK("'sheet18'!" & ADDRESS(3,8),H3)
but the following will -- links to the cell's
displayed value
=HYPERLINK("#'sheet18'!" & ADDRESS(3,8),H3)
of course if that is what you really want so would
the
following if
you didn't need the MATCH
and the following does not have addresses within
quotes
so you could change the cell or the worksheet and it
would adjust
=HYPERLINK("#"&CELL("address",teams!C3),teams!C3)

I think for your formula to work on more systems,
you
will need the
"#" included in your formula =HYPERLINK
("#'Teams'!" &
ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)

---
HTH,
David McRitchie, Microsoft MVP - Excel [site
changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm

"Frank Kabel" wrote in
message
...
Hi
maybe
=HYPERLINK("'Teams'!" &
ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)


--
Regards
Frank Kabel
Frankfurt, Germany


Tony wrote:
Hi Group,

Worked at this for ages. Can someone fix this
hyperlink
formula for me - it won't jump to the correct
worksheet
called Teams - it just goes down the worksheet that
it is
currently on called League. Also, can it be
shortened?

=HYPERLINK(MID(CELL("filename",$A$1),FIND("[",CELL
("filename",$A$1)),FIND("]",CELL("filename",$A$1))-
FIND
("[",CELL("filename",$A$1))+1) & ADDRESS(MATCH
(C3,Teams!
$A$1:$A$10000,0),1),C3)

Many thanks

Tony
.



.