Can someone fix this hyperlink formula?

T

Tony

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
 
F

Frank Kabel

Hi
maybe
=HYPERLINK("'Teams'!" & ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)
 
T

Tony

Hi Frank,

That was a quick reply.
I tried your formula but I got a message saying 'Cannot
open the specified file'.
Any suggestions?

Tony
-----Original Message-----
Hi
maybe
=HYPERLINK("'Teams'!" & ADDRESS(MATCH(C3,Teams! $A$1:$A$10000,0),1),C3)


--
Regards
Frank Kabel
Frankfurt, Germany

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
Click to expand...
.
Click to expand...
 
F

Frank Kabel

Hi
make this
=HYPERLINK("'[your_file_name.xls]Teams'!" &
ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)

--
Regards
Frank Kabel
Frankfurt, Germany

Hi Frank,

That was a quick reply.
I tried your formula but I got a message saying 'Cannot
open the specified file'.
Any suggestions?

Tony
-----Original Message-----
Hi
maybe
=HYPERLINK("'Teams'!" & ADDRESS(MATCH(C3,Teams!
$A$1:$A$10000,0),1),C3)


--
Regards
Frank Kabel
Frankfurt, Germany

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
Click to expand...
.
Click to expand...
Click to expand...
 
D

David McRitchie

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)



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


--
Regards
Frank Kabel
Frankfurt, Germany

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
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi David
thanks for this :)

--
Regards
Frank Kabel
Frankfurt, Germany


David said:
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)



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


--
Regards
Frank Kabel
Frankfurt, Germany

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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony

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 said:
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)



Frank Kabel said:
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
Click to expand...
Click to expand...
.
Click to expand...
 
D

David McRitchie

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.


Tony said:
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 said:
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)



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
Click to expand...
.
Click to expand...
Click to expand...
 
T

Tony

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
Click to expand...
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.


"Tony" <[email protected]> wrote in
Click to expand...
message news:[email protected]...
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
Click to expand...
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,
Click to expand...
you
will need the
"#" included in your formula =HYPERLINK ("#'Teams'!" &
ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)



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))-
Click to expand...
FIND
("[",CELL("filename",$A$1))+1) & ADDRESS(MATCH (C3,Teams!
$A$1:$A$10000,0),1),C3)

Many thanks

Tony
.
Click to expand...
Click to expand...


.
Click to expand...
 
D

David McRitchie

Hi Tony,

=HYPERLINK("#'Teams'!" & ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0),1),C3)

actually I guess you don't need offset, simply add 10 to the row

=HYPERLINK("#'Teams'!" & ADDRESS(MATCH(C3,Teams!$A$1:$A$10000,0)+10,1),C3)
 
T

Tony

David,

Nice one - you guessed right.

Tony
-----Original Message-----
Hi Tony,

=HYPERLINK("#'Teams'!" & ADDRESS(MATCH(C3,Teams! $A$1:$A$10000,0),1),C3)

actually I guess you don't need offset, simply add 10 to the row

=HYPERLINK("#'Teams'!" & ADDRESS(MATCH(C3,Teams! $A$1:$A$10000,0)+10,1),C3)






.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel [VBA] Check from a cell into another sheet and bring back result in MsgBox 0
Can a formula do this? 1
Hyperlink 2
Issue with File Name changing in cell 7
Hyperlink Function 10
Tab Name Reference 5
File Not Found Msg Because Sheet is Missing ? 3
Convert to Hyperlink 1

Top