Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Hi
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
The fist cell i want to look at is G23 then G65
"stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16) this formula is in row 5 "stew" wrote: The fist cell i want to look at is G23 then G65 "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
If you enter this in A1 and copy down
=(MOD(ROW(),42)-1)*42+23 then you will get 23,65, and so on... You can concatenate this to get a string representing the cell address you want eg ="R"&((MOD(ROW(),42)-1)*42+23)&"C5" will give you R23C5 Put an INDIRECT around the above like this and you get the value from cell R23C5 (on the same sheet) =INDIRECT("R"&((MOD(ROW(),42)-1)*42+23)&"C5" ,False) add the sheetname before "R" and you are home... "stew" wrote: The fist cell i want to look at is G23 then G65 "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Hi sheeloo
I am trying to use the Hyperlink in my first post with and addition to the end that will allow me to link to that work sheet at that cell. This addition has to do what you have done but how do I include that in the in the hyperlink. Thanks Stew "stew" wrote: i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16) this formula is in row 5 "stew" wrote: The fist cell i want to look at is G23 then G65 "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
You need to breakout your formulae into smaller chunks till they work. Then
you can combine them... Assuming =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" is taking you to A1 cell of the intended sheet then enter this in A1 =LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" This should give you the complete path to the sheet you want in B1 enter my formula "R"&((MOD(ROW(),42)-1)*42+23)&"C5" to get the row/col reference in R1C1 format... Now in C1 enter =A1&"!"&B1 to get a complete string in D1 enter =Indirect(C1,False) to get the value in the target cell If this works then put an Hyperlink around it in E1 =Hyperlink(Indirect(C1,False),"Descrption") Once you get the hang of it then you can combine everything into one formula. "stew" wrote: Hi sheeloo I am trying to use the Hyperlink in my first post with and addition to the end that will allow me to link to that work sheet at that cell. This addition has to do what you have done but how do I include that in the in the hyperlink. Thanks Stew "stew" wrote: i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16) this formula is in row 5 "stew" wrote: The fist cell i want to look at is G23 then G65 "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Dear Sheeloo
I'm learing fast but you will have to give me some time with this one. Wallllllllllllllllllllll Thanks Sheeloo "Sheeloo" wrote: You need to breakout your formulae into smaller chunks till they work. Then you can combine them... Assuming =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" is taking you to A1 cell of the intended sheet then enter this in A1 =LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" This should give you the complete path to the sheet you want in B1 enter my formula "R"&((MOD(ROW(),42)-1)*42+23)&"C5" to get the row/col reference in R1C1 format... Now in C1 enter =A1&"!"&B1 to get a complete string in D1 enter =Indirect(C1,False) to get the value in the target cell If this works then put an Hyperlink around it in E1 =Hyperlink(Indirect(C1,False),"Descrption") Once you get the hang of it then you can combine everything into one formula. "stew" wrote: Hi sheeloo I am trying to use the Hyperlink in my first post with and addition to the end that will allow me to link to that work sheet at that cell. This addition has to do what you have done but how do I include that in the in the hyperlink. Thanks Stew "stew" wrote: i HAVE NOW TRIED THIS AND IT WILL NOT OPEN ON THE CELL =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls"&ADDRESS(6+42*ROW()-204,(COLUMN())-16) this formula is in row 5 "stew" wrote: The fist cell i want to look at is G23 then G65 "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Hi,
With a dummy file I created a hyperlink via the Insert, Hyperlink command. Then I modified it by adding B inside the quotes and &ROW(A5) outside. HYPERLINK("Lesson12.xls!B"&ROW(A5)) See if you can start with these ideas and get something that works. -- Thanks, Shane Devenshire "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Thanks shane. I can also see the shape of how to develop this. Thanks
stew "ShaneDevenshire" wrote: Hi, With a dummy file I created a hyperlink via the Insert, Hyperlink command. Then I modified it by adding B inside the quotes and &ROW(A5) outside. HYPERLINK("Lesson12.xls!B"&ROW(A5)) See if you can start with these ideas and get something that works. -- Thanks, Shane Devenshire "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
Dear Shane / Sheeloo
I can Hyperlink to files outside my work book, by using insert hyperlink but I cannot link to a worksheet within my book no matter how I try to do it. I am using excel 2003 and Vista. Is there anything else I should be doing. If I do not hear anything from anybody I will re post with a new thread Thanks Stewart "stew" wrote: Thanks shane. I can also see the shape of how to develop this. Thanks stew "ShaneDevenshire" wrote: Hi, With a dummy file I created a hyperlink via the Insert, Hyperlink command. Then I modified it by adding B inside the quotes and &ROW(A5) outside. HYPERLINK("Lesson12.xls!B"&ROW(A5)) See if you can start with these ideas and get something that works. -- Thanks, Shane Devenshire "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Locating cell using Hyperlink
David McRitchie posted this and it might help you:
=HYPERLINK("#"&CELL("address",C5),C5) =HYPERLINK("#"&CELL("address",sheetone!C5),sheeton e!C5) =HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5) These formulas will adjust if you change the sheet name or insert/delete rows/columns on the linked sheet. stew wrote: Dear Shane / Sheeloo I can Hyperlink to files outside my work book, by using insert hyperlink but I cannot link to a worksheet within my book no matter how I try to do it. I am using excel 2003 and Vista. Is there anything else I should be doing. If I do not hear anything from anybody I will re post with a new thread Thanks Stewart "stew" wrote: Thanks shane. I can also see the shape of how to develop this. Thanks stew "ShaneDevenshire" wrote: Hi, With a dummy file I created a hyperlink via the Insert, Hyperlink command. Then I modified it by adding B inside the quotes and &ROW(A5) outside. HYPERLINK("Lesson12.xls!B"&ROW(A5)) See if you can start with these ideas and get something that works. -- Thanks, Shane Devenshire "stew" wrote: Hi =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"road managers package\tour managers spreadsheet.xls" Gets me to tour managers spread sheet. What and how would I add to locate a certain cell on that spread sheet using the row() ,5, that the above formula is in. If I drag the cell down 1 I would like it to reflect a 42 row jump. The data held in this cell repeats itself every 42 rows. Thanks for looking Stew -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating a cell within a macro | Excel Worksheet Functions | |||
Locating First and Last Cell Containing a Specific Value | Excel Worksheet Functions | |||
help locating first empty cell in a Column | Excel Worksheet Functions | |||
Locating Last Nonzero Cell in a Row | Excel Worksheet Functions | |||
Locating first blank cell | New Users to Excel |