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."

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."
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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."
>>
>>
>>

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."
>
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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."
> >>
> >>
> >>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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."
>> >>
>> >>
>> >>
>>
>>
>>