Copy worksheet with internal hyperlinks

[andy62]

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

 

[Gary''s Student]

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.

 

[andy62]

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.

 

[Ragdyer]

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.

 

[Gary''s Student]

=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

 

[Dave Peterson]

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.

 

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

=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

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.

 

[andy62]

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.

 

[andy62]

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.

=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

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.

 

[andy62]

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

=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

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.