I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

When I try that solution, it fails in the same way that the insert hyperlink
does: if, with the workbook open, I copy the worksheet to create a new one,
that formula still refers back to the original worksheet. The formula does
not update to the new sheet. I'm not talking about saving the file to a new
location, I'm trying to get the hyperlinks inside a worksheet to refer to the
new worksheet when one is created.

Any way to get that formula to do what I need it to?

TIA.

"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

Have you given any thought to using *true* XL templates.

Create your sheet (form), and then when you go to save it, expand the "Save
As Type" box and click on:
Template (*.xlt)

This places the WB in the templates folder, where you can open and use
*copies* by clicking on <File <New,
And choosing the WB name in the General tab.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andy62" wrote in message
...
When I try that solution, it fails in the same way that the insert
hyperlink
does: if, with the workbook open, I copy the worksheet to create a new
one,
that formula still refers back to the original worksheet. The formula
does
not update to the new sheet. I'm not talking about saving the file to a
new
location, I'm trying to get the hyperlinks inside a worksheet to refer to
the
new worksheet when one is created.

Any way to get that formula to do what I need it to?

TIA.

"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name
and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet
that
serves as a template; thus it is to be copied and filled in over and
over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This
imposes
major risks with my audience. How can I set up the hyperlinks so that
when
the worksheet is copied, they point to the right cells in the NEW
worksheet?
There has to be a way . . .

TIA

=HYPERLINK("#"&MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,255)&"!B9","goto")



1. copy the original worksheet
2. re-name the copy
3. save the file
This form of the equation will adapt to the copy & re-name

--
Gary''s Student - gsnu200741


"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

So you have a hyperlink in a cell in a worksheet and it points to a cell in the
same worksheet.

Maybe:
=hyperlink("#b99","click me to go to B99")

But this will always point to B99 -- no matter if you insert or delete any
rows/columns.

=HYPERLINK("#"&CELL("address",B99),"Click me")

This one will adjust if you insert/delete rows or columns.

These links stayed in the same worksheet as the formula when I copied the
worksheet.




andy62 wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

--

Dave Peterson

Watch out for these:
=CELL("filename")

It evaluates to the name of the drive/path/workbook and active sheet when excel
recalculated.

Better is to make sure that the function includes a reference to the worksheet
you want:

=CELL("filename", A1)
A1 can be any cell in that worksheet--including the cell with the formula.



Gary''s Student wrote:

=HYPERLINK("#"&MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,255)&"!B9","goto")

1. copy the original worksheet
2. re-name the copy
3. save the file
This form of the equation will adapt to the copy & re-name

--
Gary''s Student - gsnu200741

"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

--

Dave Peterson

The person I am helping needs to have one file wherein one worksheet can be
cloned over and over inside the same workbook. So that one worksheet is the
"template", but the file is not set up as a template.

Thanks . . . the search continues.

"Ragdyer" wrote:

Have you given any thought to using *true* XL templates.

Create your sheet (form), and then when you go to save it, expand the "Save
As Type" box and click on:
Template (*.xlt)

This places the WB in the templates folder, where you can open and use
*copies* by clicking on <File <New,
And choosing the WB name in the General tab.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"andy62" wrote in message
...
When I try that solution, it fails in the same way that the insert
hyperlink
does: if, with the workbook open, I copy the worksheet to create a new
one,
that formula still refers back to the original worksheet. The formula
does
not update to the new sheet. I'm not talking about saving the file to a
new
location, I'm trying to get the hyperlinks inside a worksheet to refer to
the
new worksheet when one is created.

Any way to get that formula to do what I need it to?

TIA.

"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name
and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet
that
serves as a template; thus it is to be copied and filled in over and
over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This
imposes
major risks with my audience. How can I set up the hyperlinks so that
when
the worksheet is copied, they point to the right cells in the NEW
worksheet?
There has to be a way . . .

TIA

Thanks, but that approach still doesn't work. I think everyone is confused
about the problem I am having. I have only one file, and it never gets
renamed. Within that file I have a worksheet that gets copied within the
workbook. All the hyperlinks within that copied worksheet point back to the
original worksheet instead of the copy.

"Gary''s Student" wrote:

=HYPERLINK("#"&MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,255)&"!B9","goto")



1. copy the original worksheet
2. re-name the copy
3. save the file
This form of the equation will adapt to the copy & re-name

--
Gary''s Student - gsnu200741


"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA

Disregard my other note, this solution did work, Gary"s Student. I tested it
before saving the file. Thank you!

"Gary''s Student" wrote:

=HYPERLINK("#"&MID(CELL("filename"),FIND("]",CELL("filename"),1)+1,255)&"!B9","goto")



1. copy the original worksheet
2. re-name the copy
3. save the file
This form of the equation will adapt to the copy & re-name

--
Gary''s Student - gsnu200741


"Gary''s Student" wrote:

Do not create hyperlinks using:
Insert Hyperlink
Instead use something like:

=HYPERLINK("#Sheet2!B9","goto")

This link will always be local. You can save theworkbook with any name and
the link will always take you to the local Sheet2.
--
Gary''s Student - gsnu200741


"andy62" wrote:

I have seen this question asked but not answered. I have a worksheet that
serves as a template; thus it is to be copied and filled in over and over.
It includes a couple hyperlinks for navigation. When the worksheet is
copied, the links point to the template, not to the new sheet. This imposes
major risks with my audience. How can I set up the hyperlinks so that when
the worksheet is copied, they point to the right cells in the NEW worksheet?
There has to be a way . . .

TIA