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

Re:  "That's done it"

You are welcome.
Please consider marking the answer as an answer.

NLtL

Answer 3

I'm afraid I have another question! The links are working well within the sheet so no problem there. The issue comes when I open a new workbook/spreadsheet while working on this one. If another workbook is opened the links try to redirect to that, and not the cell in the workbook it lives in! So to clarify the problem:

• Workbook 1 has the hyperlinks as set out above.
• Workbook open. Click hyperlink. Cursor moves to the correct cell in Workbook 1.
• Open another workbook to look at some other data. (Workbook 2)
• Click hyperlink in workbook 1. Cursor moves to a cell in Workbook 2.

I see the problem - the formula in the cell holding the file name is reading the most recent file opened. So for example while WB1 is open the result of the formula says "Workbook 1" but once I open the other workbook it changes to say "Workbook 2". I'm using this formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Why is it reading the latest opened file and not the one it is in? The only way I can fix this is to go into the cell which holds the formula, click into the formula (not change it), and press enter. This brings the filename back to the WB1 name and the links work correctly again.

What's going on and how do I fix it? 🤷‍♀️

Answer 4

Re: Use of CELL function

The CELL function syntax is: =CELL(info_type, [reference])

(the brackets around 'reference' indicate the argument is optional)

"reference" is the cell you want information about.

If you omit the reference then the active cell is used.

Note: An active cell occurs in the active workbook.

This worked for me...

=MID("C:\Excel Folder\General_2[One_Drive_Links.xlsm]Links to One Drive",SEARCH("[",CELL("filename",A1))+1, SEARCH("]",CELL("filename",A1))-SEARCH("[",CELL("filename",A1))-1)

Note: The CELL function was originally used in XL4 macros. The MS book that describes their use has a MS 1992 copyright. The XL2010 (Excel 14) help file includes the CELL function.

'---
NLtL

https://1drv.ms/u/s!Au8Lyt79SOuhZ\_2VvKCLZxz9iwI?e=vnEabM

Add_Table of Contents, Calculate Payments, Custom_Functions, Professional_Compare

Answer 5

Hello,

Thank you for that. I think I get it. Unfortunately it didn't work though. Once I open a new spreadsheet the link still tries to jump across to that.