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,

Thanks for your updates. 

Could you save the file in an online place and share an anonymous link in the private message?

Best Regards,

Cliff

Answer 3

Dear Terri,

Welcome to share any updates when you have time if you need further help on this issue.

Best Regards,

Cliff