Copying sheets with internal links

New Contributor

Hello!

 

I was hoping someone could help me find a more efficient way to copy sheets with internal links. 

 

When I copy/duplicate a sheet, any internal links held within that sheet will bounce back to the original sheet and not the copied one. Example: Link to Sheet1!A1 when duplicated, in sheet 2 that link will then move me back to sheet1!A1. I was also told that changing the name of the sheet would alter the links but it did not work. (I am not manually copying data but rather copying the whole sheet)

 

I can't seem to find any way to fix, and any help would be appreciated!

3 Replies

@Ana_561 

Example: Link to Sheet1!A1 when duplicated, in sheet 2 that link will then move me back to sheet1!A1

 

Yes, if the reference in Sheet1, in cell whatever is literally to "Sheet1!A1" then that will remain as a reference to Sheet1.

 

I was also told that changing the name of the sheet would alter the links but it did not work

 

I'm going to re-state that as "I was told that copying to a new sheet would alter the links" which would be true IF the original reference, rather than to "Sheet1!A1," was simply to A1 or $A$1... if it were that simple, then it would be referring to A1 in the new, copied sheet.

 

So why don't you back up and give us a more complete description of the bigger picture here.  For example (but feel free to go beyond these questions):

  1. What (without revealing confidential info) is the nature of the sheet being copied?
  2. (While you're at it, why is it being copied...is it a sheet of monthly data, for example, or a sheet that refers to different departments in an organization where the bigger workbook contains sheets for each month or each department (or whatever))
  3. Are there some references that you want to stay constant (to a front "master sheet" for example) and others that are to be relative to the sheet itself?

 

 

@mathetes Thank you for the response!

 

In terms of references, when I created the excel sheet, I only referenced A1 not Sheet1!A1. However, when I copy the sheet, the reference changes from A1 to Sheet1!A1. Would it be more efficient to reference $A$1? Please note that these references are hyper links, they are being used to jump from one side of page to another as it is a rather large amount of data. 

 

To answer you questions I am doing a semester long breakdown, and I was hoping to create a template that could then be duplicated for every week needed. The sheet has multiple internal links, but no links that go outside of that one individual sheet.

 

Hope this answered your questions!

 

 

@Ana_561 

 

Oh my. I have no experience using references like that as hyperlinks. Happy to learn, but that puts your inquiry into a different ball park.

 

Is it possible for you to post a copy of the workbook on OneDrive or GoogleDrive, and post a link here so that I and others can actually take a look at it?