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 - |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
Well, one thing is are you sure you have spelt the names of the folder, file
and sheet correctly? You have Merchandise Package as the folder name, but you use Mechandise for both the filename and sheetname - is there an "r" missing? If you remove the ,"view" from the end of the formula then it will show the complete path in the cell (you might need to widen it to see all of it), so check this out thoroughly to see that it matches exactly where you want to go to. Hope this helps. Pete "stew" wrote in message ... 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 - |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
well pete
=HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Merchandise Spreadsheet.xls]Merchandise Spreadsheet'!A"&((ROW()-4)*103+8),"View") The link was written properly it was just the I had Mispelling mistake however all corrected and Still Same Problem. When I take out View it is directing me to the correct place and cell? " An unexpected error has occurred" By the way, Just in case I don't hear from you, and I would not blame you, Thanks for all your help Best Stew "Pete_UK" wrote: Well, one thing is are you sure you have spelt the names of the folder, file and sheet correctly? You have Merchandise Package as the folder name, but you use Mechandise for both the filename and sheetname - is there an "r" missing? If you remove the ,"view" from the end of the formula then it will show the complete path in the cell (you might need to widen it to see all of it), so check this out thoroughly to see that it matches exactly where you want to go to. Hope this helps. Pete "stew" wrote in message ... 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 - |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
Okay, have experimented and researched a bit - the square brackets need to
encompass the full path and filename, and the apostrophes only the sheetname, so try this: =HYPERLINK("["&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\Merchandise Spreadsheet.xls]'Merchandise Spreadsheet'!A"&((ROW()-4)*103+8),"View") Hope this helps. Pete "stew" wrote in message ... well pete =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Merchandise Spreadsheet.xls]Merchandise Spreadsheet'!A"&((ROW()-4)*103+8),"View") The link was written properly it was just the I had Mispelling mistake however all corrected and Still Same Problem. When I take out View it is directing me to the correct place and cell? " An unexpected error has occurred" By the way, Just in case I don't hear from you, and I would not blame you, Thanks for all your help Best Stew "Pete_UK" wrote: Well, one thing is are you sure you have spelt the names of the folder, file and sheet correctly? You have Merchandise Package as the folder name, but you use Mechandise for both the filename and sheetname - is there an "r" missing? If you remove the ,"view" from the end of the formula then it will show the complete path in the cell (you might need to widen it to see all of it), so check this out thoroughly to see that it matches exactly where you want to go to. Hope this helps. Pete "stew" wrote in message ... 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 - |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
Eureka!!!!
Heaven and Bliss Thank you pete for sticking with it. This problem has been with me for days Thanks for all your help Stewart "Pete_UK" wrote: Okay, have experimented and researched a bit - the square brackets need to encompass the full path and filename, and the apostrophes only the sheetname, so try this: =HYPERLINK("["&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\Merchandise Spreadsheet.xls]'Merchandise Spreadsheet'!A"&((ROW()-4)*103+8),"View") Hope this helps. Pete "stew" wrote in message ... well pete =HYPERLINK("'"&LEFT(CELL("Filename",A1),SEARCH("[",CELL("filename",A1))-25)&"Merchandise Package\[Merchandise Spreadsheet.xls]Merchandise Spreadsheet'!A"&((ROW()-4)*103+8),"View") The link was written properly it was just the I had Mispelling mistake however all corrected and Still Same Problem. When I take out View it is directing me to the correct place and cell? " An unexpected error has occurred" By the way, Just in case I don't hear from you, and I would not blame you, Thanks for all your help Best Stew "Pete_UK" wrote: Well, one thing is are you sure you have spelt the names of the folder, file and sheet correctly? You have Merchandise Package as the folder name, but you use Mechandise for both the filename and sheetname - is there an "r" missing? If you remove the ,"view" from the end of the formula then it will show the complete path in the cell (you might need to widen it to see all of it), so check this out thoroughly to see that it matches exactly where you want to go to. Hope this helps. Pete "stew" wrote in message ... 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 - |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
Well, I'm so very pleased to hear that, Stew.
I should have remembered from a thread I contributed to in August that the syntax for hyperlinks is slightly different than for other Excel functions. Glad we got there in the end !! Pete On Nov 5, 6:44*pm, stew wrote: Eureka!!!! Heaven and Bliss Thank you pete for sticking with it. This problem has been with me for days Thanks for all your help Stewart |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
Dear Pete
I am new to this and I am treating as my " Burst of Creativity in the twlight years". I love the learning process. Best Hobby I ever Had Thanks Again, Look Out For me , I'll Be Back Stew "Pete_UK" wrote: Well, I'm so very pleased to hear that, Stew. I should have remembered from a thread I contributed to in August that the syntax for hyperlinks is slightly different than for other Excel functions. Glad we got there in the end !! Pete On Nov 5, 6:44 pm, stew wrote: Eureka!!!! Heaven and Bliss Thank you pete for sticking with it. This problem has been with me for days Thanks for all your help Stewart |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Hyperlink update
I agree, Stew, though sometimes I am reminded that my memory is not as
good as it used to be !! <bg Pete On Nov 5, 7:08*pm, stew wrote: Dear Pete I am new to this and I am treating as my " Burst of Creativity in the twlight years". I love the learning process. Best Hobby I ever Had Thanks Again, Look Out For me , I'll Be Back Stew "Pete_UK" wrote: Well, I'm so very pleased to hear that, Stew. I should have remembered from a thread I contributed to in August that the syntax for hyperlinks is slightly different than for other Excel functions. Glad we got there in the end !! Pete On Nov 5, 6:44 pm, stew wrote: Eureka!!!! Heaven and Bliss Thank you pete for sticking with it. This problem has been with me for days Thanks for all your help Stewart- 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) |