| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink
Hi all
In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"Merchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink
Hello again, Stew.
You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"M* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29*pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"M*erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink
Dear Pete
Thanks again for looking at this =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" exactly as you see it gets me to Merchandise spreadsheet. If I add \. IT ALSO DOES THE JOB IF I ADD THE CHANGES IN THE WAY YOU SUGGEST IT WILL NOT ACCEPT THE FORMULA. iF i REMOVE "vIEW" THE FORMULA IS ACCEPTED BUT WHEN I TRY TO USE COMES BACK WITH THE PROMPT " CANNOT OPEN SPECIFIED FILE". THE PATH SHOWS CORRECTLLY ANY THOUGHTS sTEW "Pete_UK" wrote: Hello again, Stew. You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29 pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink update
Hi Pete
=HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'! A"&((ROW()-4)*103+8),"View" is accepted as a formula and View shows up. THE PATH SHOWS UP AS TAKING ME TO A111 WHICH IS THE CORRECT CELL BUT STILL THE PROMPT " CANNOT OPEN SPECIFIED FILE" COMES UP "stew" wrote: Dear Pete Thanks again for looking at this =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" exactly as you see it gets me to Merchandise spreadsheet. If I add \. IT ALSO DOES THE JOB IF I ADD THE CHANGES IN THE WAY YOU SUGGEST IT WILL NOT ACCEPT THE FORMULA. iF i REMOVE "vIEW" THE FORMULA IS ACCEPTED BUT WHEN I TRY TO USE COMES BACK WITH THE PROMPT " CANNOT OPEN SPECIFIED FILE". THE PATH SHOWS CORRECTLLY ANY THOUGHTS sTEW "Pete_UK" wrote: Hello again, Stew. You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29 pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink update
Sorry Pete
This is accepted formula =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'! A"&((ROW()-4)*103+8),"View") THE PATH SHOWS UP AS TAKING ME TO A111 WHICH IS THE CORRECT CELL BUT STILL THE PROMPT " CANNOT OPEN SPECIFIED FILE" COMES UP ANY THOUGHTS STEW "stew" wrote: Hi Pete =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'! A"&((ROW()-4)*103+8),"View" is accepted as a formula and View shows up. THE PATH SHOWS UP AS TAKING ME TO A111 WHICH IS THE CORRECT CELL BUT STILL THE PROMPT " CANNOT OPEN SPECIFIED FILE" COMES UP "stew" wrote: Dear Pete Thanks again for looking at this =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" exactly as you see it gets me to Merchandise spreadsheet. If I add \. IT ALSO DOES THE JOB IF I ADD THE CHANGES IN THE WAY YOU SUGGEST IT WILL NOT ACCEPT THE FORMULA. iF i REMOVE "vIEW" THE FORMULA IS ACCEPTED BUT WHEN I TRY TO USE COMES BACK WITH THE PROMPT " CANNOT OPEN SPECIFIED FILE". THE PATH SHOWS CORRECTLLY ANY THOUGHTS sTEW "Pete_UK" wrote: Hello again, Stew. You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29 pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink update
If you check out the formula I gave you then immediately after the
=HYPERLINK( part I had "'"& (quotes apostrophe quotes). This adds in the apostrophe to match with the one at the end of the sheet name. Hope this helps. Pete On Nov 5, 9:52*am, stew wrote: Hi Pete =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Me*rchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'! A"&((ROW()-4)*103+8),"View" is accepted as a formula and View shows up. THE PATH SHOWS UP AS TAKING ME TO A111 WHICH IS THE CORRECT CELL BUT STILL THE PROMPT " CANNOT OPEN SPECIFIED FILE" COMES UP "stew" wrote: Dear Pete Thanks again for looking at this =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"M**erchandise Package\Mechandise Spreadsheet.xls" exactly as you see it gets me to Merchandise spreadsheet. If I add \. *IT ALSO DOES THE JOB IF I ADD THE CHANGES IN THE WAY YOU SUGGEST IT WILL NOT ACCEPT THE FORMULA. iF i REMOVE "vIEW" THE FORMULA IS ACCEPTED BUT WHEN I TRY TO USE COMES BACK WITH THE PROMPT " CANNOT OPEN SPECIFIED FILE". THE PATH SHOWS CORRECTLLY ANY THOUGHTS sTEW "Pete_UK" wrote: Hello again, Stew. You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)*)&"M* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29 pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"M**erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Hyperlink update
Hi Pete
The formula below has been accepted =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'!A"&((ROW()-4)*103+8),"view") This has changed the prompt to " an unexpected error has occurred" I know we are so near to getting this which will help me tremendously but!! so far Any more thoughts stew "Pete_UK" wrote: If you check out the formula I gave you then immediately after the =HYPERLINK( part I had "'"& (quotes apostrophe quotes). This adds in the apostrophe to match with the one at the end of the sheet name. Hope this helps. Pete On Nov 5, 9:52 am, stew wrote: Hi Pete =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"MeÂ*rchandise Package\[Mechandise Spreadsheet.xls]Mechandise Spreadsheet'! A"&((ROW()-4)*103+8),"View" is accepted as a formula and View shows up. THE PATH SHOWS UP AS TAKING ME TO A111 WHICH IS THE CORRECT CELL BUT STILL THE PROMPT " CANNOT OPEN SPECIFIED FILE" COMES UP "stew" wrote: Dear Pete Thanks again for looking at this =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*Â*erchandise Package\Mechandise Spreadsheet.xls" exactly as you see it gets me to Merchandise spreadsheet. If I add \. IT ALSO DOES THE JOB IF I ADD THE CHANGES IN THE WAY YOU SUGGEST IT WILL NOT ACCEPT THE FORMULA. iF i REMOVE "vIEW" THE FORMULA IS ACCEPTED BUT WHEN I TRY TO USE COMES BACK WITH THE PROMPT " CANNOT OPEN SPECIFIED FILE". THE PATH SHOWS CORRECTLLY ANY THOUGHTS sTEW "Pete_UK" wrote: Hello again, Stew. You will need the sheet name as well as the column letter and row number, but you will also need to have square brackets around the filename and two apostrophes. So, try this: =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)Â*)&"MÂ* erchandise Package\[Mechandise Spreadsheet.xls]Sheet1'! A"&((ROW()-4)*103+8),"View") I assume that the LEFT function does return the full path to that point and ends in \. Hope this helps. Pete On Nov 4, 8:29 pm, stew wrote: Hi all In this Formula how would one add the exact cell you would want to open at =HYPERLINK(LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25))&"MÂ*Â*erchandise Package\Mechandise Spreadsheet.xls" Ideally it would be along the lines of "&((ROW()-4)*103+8),"View") Thanks Stew- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can't make hyperlink function work for hyperlink to website | Excel Worksheet Functions | |||
| How do I create a hyperlink to a cell with the hyperlink function | Excel Worksheet Functions | |||
| Moving rows with Hyperlink doesn't move hyperlink address | Excel Discussion (Misc queries) | |||
| Hyperlink from one sheet to the hyperlink on another | Excel Discussion (Misc queries) | |||
| Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
Hybrid Mode
