Copy excel tab and maintain hyperlinks within that tab

Question

Hello,

I have a worksheet that has a set of cells at the top that are hyperlinked to set places in the sheet. This document is used all day every day and it's a pretty big spreadsheet so to scroll through to find a section is really impractical. I need to have multiple versions of the same tab within the workbook. When I make a copy of the tab though the hyperlinks still link through to the original reference cell, not to the cell in the new tab. So, to try and explain this better.......

Tab 1

Cell A2 has a hyperlink to cell B30 in tab 1

Cell A3 has a hyperlink to cell D442 in tab 1

etc etc

Right click on tab, choose 'Move or Copy', select copy and choose where I want to put the duplicate sheet. New tab created called 'Tab 1 (2)'. Rename this to Tab 2. Now.....

Tab 2 

Cell A2 has a hyperlink to cell B30 in tab 1

Cell A3 has a hyperlink to cell D442 in tab 1

etc etc

The hyperlinks are still routing to the original cell in tab 1. Tab 2 hyperlinks need to go to the cell in Tab 2. I need about 30 of these duplicated tabs and there are 12 hyperlinks at the top of each tab. Surely I don't have to manually edit every single one??

Please someone save my sanity.........

Answer 1

Re: copying worksheets with hyperlinks

For what it is worth the Hyperlink function does what you want...
  =HYPERLINK("[Book1.xlsm]B22",694)

In the above formula...

  B22 is the destination cell and 694 is the value of the cell containing the hyperlink.
  Note the placement of the quote marks.

NLtL https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O

Answer 2

Dear Terri,

Have you tried Nothing Left to Lose's suggestions? Welcome to share any updates when you have time if you need further help on this issue.

@Nothing Left to Lose, thanks for sharing the suggestions and information here. About the link and the files you provided here, please add your confirmation about whether they can be shared here directly and I'll remove them to protect your privacy if there is no confirmation from you to protect your privacy after 24 hours.

Thanks for your understanding.

Cliff

Answer 3

To:  Cliff Gu

Appreciate your concern about the links.
I would like the links to remain as is.  Please do not delete them.

NLtL

Answer 4

Dear Terri,

Welcome to the forum here.

I do a test and it seems that the hyperlinks are pointed to the newly copied tab in my environment. I create Tab1, copy Tab1 as Tab2 and copy Tab2 as Tab3. In Tab1, A2: =B30 and A3:=D30. In Tab2 and Tab3,  A2: =B30 and A3:=D30. If I change the value in B30 in Tab2, the value in A2 will be changed. The value in Tab1 will not be affected.

So about "Right click on tab, choose 'Move or Copy', select copy and choose where I want to put the duplicate sheet. New tab created called 'Tab 1 (2)'. Rename this to Tab 2. Now.....Tab 2  Cell A2 has a hyperlink to cell B30 in tab 1 Cell A3 has a hyperlink to cell D442 in tab 1 etc etc", I'd like you to upload some related screenshots for our confirmation.

Meanwhile, upload a full screenshot of the Account page in the Excel application you are using for our reference as well. You can access the page via clicking on File>Account.

Best Regards,

Cliff

Answer 5

Hello,

Thank you for the reply but that's not quite what I meant. It's not about updating data in the cells, it's about how hyperlinks function. I have set up a test excel sheet so that I can screenshot it for you.

This is Tab 1. Clicking in cell C5 should move the cursor to cell C16. It's just a basic hyperlink.

This is Tab 2. Everything has copied over and the layout is the same.
Clicking cell C5 in tab 2 should now take me to cell C16 in Tab 2.

As you can see from the formula though it still wants to take me to C16 in Tab 1.

So if I now created a Tab 3 the links in that sheet would still try to take me to Tab 1.

I need that hyperlink to update to the new sheet, not the original location in the original tab. I don't want to have to update all the hyperlinks manually.