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

Hmmm....At least I'm predictable in a way that I approve.😉

I'll reiterate that the BudgetPivot sheet is really a simple example, far simpler than those other two that you've seen. But to a great extent that simplicity is precisely because Pivot Tables are simple, yet can work with and helpfully summarize massive (transactional or other) databases.

My guess is that those two extra features of your monthly sheets status quo--each bill's paid amount and date, as well as the virtual savings accounts--especially the former, could easily be incorporated into a single database. The latter--those virtual savings accounts--might warrant their own but still single database.

Welcome to the ranks of the retired; I joined that happy company in late 2002, nearly a quarter century ago. Since you're now retired, I'm going to assume you're in your 60s...so maybe you were around when Pivot Tables (and spreadsheets) were in their toddlerhood, as was I. I actually was writing my own programs in the early 1970s, creating and manipulating multidimensional arrays (using the APL programming language), so spreadsheets like Lotus and Excel (along the way VisiCalc and Quattro) were welcome shortcuts. That's one of the reasons I look at things from a basic design perspective.......

Mathetes - great to hear from you!  Hope you are well this summer.  You may remember me as the guy who tracks all the teas he buys.  I wanted a way to use a worksheet name in the title cell of said worksheet.

It's so funny...I knew that if you read my post, you would ask why I do separate worksheets!  I even thought about answering it prophylactically in my initial post, but decided to just pose the question.

I will review your response in more detail, but my initial answer is that each monthly worksheet has a section (that I didn't put in my attachment) that is month specific.  It contains the paid amount and paid date of each bill that I must pay each month, as well as the savings "virtual" accounts with target amount and transfer date to savings.

But as I say, I will study your idea, as I am sure there is a more efficient way to track my finances.  I have been doing it this way for at least 15 years and it has kept me on track, but now that I am retired, I have time to study a new way!  You have sent me a few really sophisticated workbooks (Federal_Tax_Calculator and ForecastEOY_Balances for example) so I look forward to BudgetPivot.

NikolinoDE - thank you for including an attachment with the formula in it, as I was having trouble deciphering all the quotes and apostrophes.  I will work with your formula later today and let you know how it worked out.

I think a short term fix may be NicokinoDE's formula and the long-term fix may be a redesign.

Thanks to both of you!

May I answer your question with a question?

Why do you have a separate sheet for each month? 

Now, what lies behind that first and primary question is this: in general, unless there's some compelling reason, Excel can work far FAR! more efficiently with a single database of transactions, so long as those transactions are stored with date, amount, (and such subordinate headings as "source," "budget category 1,"  "budget category 2", etc.

A single database can serve as the source for a Pivot Table that breaks out and summarizes the details by month, by category. I've attached a very simple example. On a more personal basis, I have a far more extensive transactional database--holding transactions for multiple years, multiple bank and credit card accounts--and the Pivot Table is THE tool I use to summarize all of those at whatever level of detail I wish. The Pivot Table has been, since it first appeared on the spreadsheet scene at least 40 years ago (I can recall using that tool in the 1980s), one of the most powerful and popular reporting methods. Here is an excellent resource to learn more about Pivot Tables. YouTube also has a wealth of videos on the subject.

Nowadays, there are even more customizable ways to create dynamic summaries of transactional data--such as you have here--producing "dashboard summaries" by category, by month, etc. The Dynamic Array functions come to mind.

Bottom line: I highly recommend that you consider a total redesign. It will be easier to maintain, and far easier to extract meaningful summaries.

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.