| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone fix this hyperlink formula?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 Tony wrote: 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 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 . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 |
|
#6
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 . |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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 . . |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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) --- 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 ... Yes, I would like to include the offset function 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? |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Can someone fix this hyperlink formula?
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) --- 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 ... Yes, I would like to include the offset function 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? . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| hyperlink formula | Excel Worksheet Functions | |||
| Hyperlink formula | Excel Worksheet Functions | |||
| Hyperlink formula | Excel Worksheet Functions | |||
| Hyperlink Formula | Excel Discussion (Misc queries) | |||
| hyperlink formula | Excel Discussion (Misc queries) | |||
Linear Mode
