Fixing Hyperlinks in a Copied Worksheet (on an Apple Mac)

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:

1. In each of your 23 hyperlink cells, delete the existing hyperlink and instead type in the formula above.
2. 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.).
3. 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.