Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
I have a requirement to dynamically build/create a hyperlink in a workbook
which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
From my understanding, the HYPERLINK formula always needs the name of the
book, even when creating links to places in the same document. I have created this link in "Book1", and it works dinamically with the contents of cell B1: =HYPERLINK("[Book1]" & B1 & "!A1",B1) You may change any part you need in the formula to direct the link to the proper cell, and to show the name you prefer. Hope this helps, Miguel. "Larry S" wrote: I have a requirement to dynamically build/create a hyperlink in a workbook which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
Thanks - I tried your advice, and couldn't get it to work so I built a file
like you outlined below, and that also didn't work...when you click on the dynmaic link, you get an error that it can't open the file. Any suggestions?? I have enclosed the sample. "Miguel Zapico" wrote in message ... From my understanding, the HYPERLINK formula always needs the name of the book, even when creating links to places in the same document. I have created this link in "Book1", and it works dinamically with the contents of cell B1: =HYPERLINK("[Book1]" & B1 & "!A1",B1) You may change any part you need in the formula to direct the link to the proper cell, and to show the name you prefer. Hope this helps, Miguel. "Larry S" wrote: I have a requirement to dynamically build/create a hyperlink in a workbook which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
Larry:
You could create a navigation sheet with hyperlinks which is preferable when you need descritions what can be found in the book. However, if the sheet names are self-explaining a user can navigate by right-clicking the "video control buttons" in the left under part of yr screen, before the first tab divider name: a list of all worksheets pop-up and can be quickly accessed. GL, Henk "Larry S" wrote: I have a requirement to dynamically build/create a hyperlink in a workbook which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
I am using Excel 2003, I don't know if it would work on other versions.
Maybe a basic test that can be done is to write this formula in the sheet2 of a newly created workbook: =HYPERLINK("[Book1]Sheet1!A1","Test") If this doesn't work, and the names of the book and the sheet are right, you may not be able to use the HYPERLINK formula in your workbook, and require VBA instead. Miguel. "Larry S" wrote: Thanks - I tried your advice, and couldn't get it to work so I built a file like you outlined below, and that also didn't work...when you click on the dynmaic link, you get an error that it can't open the file. Any suggestions?? I have enclosed the sample. "Miguel Zapico" wrote in message ... From my understanding, the HYPERLINK formula always needs the name of the book, even when creating links to places in the same document. I have created this link in "Book1", and it works dinamically with the contents of cell B1: =HYPERLINK("[Book1]" & B1 & "!A1",B1) You may change any part you need in the formula to direct the link to the proper cell, and to show the name you prefer. Hope this helps, Miguel. "Larry S" wrote: I have a requirement to dynamically build/create a hyperlink in a workbook which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Hyperlink
Thanks for the advice...I'm also using EXCEL 2003. I tried your
recommendation and found it worked....until I saved the file and then I began to get the same results I was getting prior to posting the question in the forum. After working with it a few minutes, I uncovered the issue....placing the file extention in the workbook name corrected the problem. The formula that finally worked is: =HYPERLINK("[Book1.xls]Sheet1!A1","Test") Note the ".xls" added to the file name. Thanks for the help. HEK, thanks for your suggestion also, looks like I'm set. "Miguel Zapico" wrote in message ... I am using Excel 2003, I don't know if it would work on other versions. Maybe a basic test that can be done is to write this formula in the sheet2 of a newly created workbook: =HYPERLINK("[Book1]Sheet1!A1","Test") If this doesn't work, and the names of the book and the sheet are right, you may not be able to use the HYPERLINK formula in your workbook, and require VBA instead. Miguel. "Larry S" wrote: Thanks - I tried your advice, and couldn't get it to work so I built a file like you outlined below, and that also didn't work...when you click on the dynmaic link, you get an error that it can't open the file. Any suggestions?? I have enclosed the sample. "Miguel Zapico" wrote in message ... From my understanding, the HYPERLINK formula always needs the name of the book, even when creating links to places in the same document. I have created this link in "Book1", and it works dinamically with the contents of cell B1: =HYPERLINK("[Book1]" & B1 & "!A1",B1) You may change any part you need in the formula to direct the link to the proper cell, and to show the name you prefer. Hope this helps, Miguel. "Larry S" wrote: I have a requirement to dynamically build/create a hyperlink in a workbook which would allow the user to quickly move between worksheets. ALL informataion is contained within the workbook so there is no need (or desire) to have any external links. The workbook would be laid out as follows: Worksheets: Summary Project 1 Project 2 Project 3 Each of the Project worksheets would have a project title located in cell I8 On the summary worksheet, each row would provide summary information pulled directly off the Project worksheet. The worksheet name would be place in column N therefore all pulls of information from that project would be done referencing the worksheet name found in column N. The requirement would be to dynamically build a hyperlink from the summary worksheet to the respective project worksheet with the link being effectively the title of the project (pulled for the project worksheet) and the linke place in column B. I have tried the HYPERLINK function as the help text implies this will work - any suggestions?? - Here is information from the Help Text: You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56. =HYPERLINK("[Budget]June!E56", E56) To jump to a different sheet in the same workbook, change the name of the sheet in the link. In the previous example, to create a link to cell E56 on the September sheet, change the word "June" to "September." |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink function fails when using variable row number | Excel Worksheet Functions | |||
answer to odd results when comparing hyperlink addresses | Links and Linking in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Using the Hyperlink Function and finding filenames | Excel Worksheet Functions | |||
Can I create a dynamic email address in Excels hyperlink? | Excel Worksheet Functions |