Forum Discussion
Fixing Hyperlinks in a Copied Worksheet (on an Apple Mac)
Depending on what your desired end goal is, those lists of account transactions--I'm assuming that you're looking at some summary or extraction of a defined sub-set of data from the comprehensive database--you might find it more suitable to use a few of the Dynamic Array functions. Let me direct your attention to FILTER, UNIQUE, SORT. Also, this probably could be used to create a "custom" Pivot Table, PIVOTBY. I haven't used this last function yet; I have used the first three extensively, and found it very easy to create custom extractions of data on the spot, with (yes) a lot more flexibility than the Pivot Table tool itself gives you.
Hi, you're right that the usual fixes (helper columns, Edit Links) don't apply here since these are internal, same-workbook jumps rather than external URLs.
Why this happens: When you use Insert → Link (or right-click → Hyperlink) to point at a cell, Excel bakes the sheet name directly into that hyperlink at the moment you create it. When you later copy the whole worksheet, Excel duplicates the hyperlink as-is — it doesn't know you'd want it to now point at the new sheet, so it keeps referring back to the original one.
The fix: swap your hyperlinks for a formula that looks up its own sheet name automatically, instead of one that has a sheet name hardcoded into it. That way, whichever sheet the formula happens to live on — the original, or any future copy of it — it always points to a cell on that same sheet.
Here's the formula, for a link that should jump to cell D2 on the same sheet and display the text "Account A":
=HYPERLINK("#'"&SUBSTITUTE(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),"'","''")&"'!D2","Account A")
You don't need to understand every piece of it — just the idea: CELL("filename") reports back which sheet the formula is sitting on right now, so the link rebuilds itself using the current sheet's name every time it recalculates.
How to set it up:
- In each of your 23 hyperlink cells, delete the existing hyperlink and instead type in the formula above.
- Just change two things each time: the destination cell reference (D2, G2, D11, etc. — whatever cell that link should jump to) and the display text in the quotes at the end ("Account A", "Account B", etc.).
- It's a bit of one-time setup work to convert all 23, but that's the last time you'll ever have to touch them.
After that: every month, when you copy the sheet and rename it, the links will automatically follow along to the new sheet — no editing required.
One small Mac note: if a copied link doesn't seem to update right away, press Cmd+Shift+F9 (force recalculate) — Excel on Mac occasionally needs a nudge to re-evaluate CELL("filename") right after a sheet copy.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.