Dynamic Hyperlink

[Larry S]

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

 

[Miguel Zapico]

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.

 

[HEK]

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

 

[Miguel Zapico]

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.

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.

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.

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 S]

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.

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.

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.

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.

:

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