Posted to microsoft.public.excel.programming
|
|
Can someone fix this hyperlink formula?
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
|