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" <MiguelZapico@discussions.microsoft.com> wrote in message
> news:3928F509-8C7E-4C32-A922-B91F02BDBB9F@microsoft.com...
> > 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" <MiguelZapico@discussions.microsoft.com> wrote in message
news:0733B44C-73A5-4805-88E5-497137D67F91@microsoft.com...
>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" <MiguelZapico@discussions.microsoft.com> wrote in message
>> news:3928F509-8C7E-4C32-A922-B91F02BDBB9F@microsoft.com...
>> > 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."
>> >>
>> >>
>> >>
>>
>>
>>