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

Yes to your question: off to the right in the transaction table, the very last column, I make sure that all expense categories are negatives. (It is interesting, isn't it, that some financial institutions report them as negatives, others as positives, so it's up to us to make them consistent.)

FYI, here are the columns in my transactional database.

• Account: the relevant bank or credit card account (name, not number)
• Date: self explanatory
• Action: the description that comes when I download the data from the bank or credit card co.
• Add'l description (some accounts give add'l info, sometimes helpful in categorizing sometimes confusing
• Category1 (High level budget category)
• Category2 (Medium level budget category)
• Sub (lowest level budget category; not always used; but useful if I want to differentiate get more details that Category2 provides. For example, if Category2 is "Video Streaming" then Sub might be where I'd note Apple, HBOMax, Prime, etc.
• Abs: that aforementioned calculation to render all expense items as negatives.

To assure data integrity, I use data validation to create drop down menus for Budget Categories 1 and 2. I've designed it so I can add new categories if the need arises. Those tables are complicated; In addition to roughly 10 types of "income", I actually have over 140 buckets to put expenses into, and those are grouped under "Charity" "Fixed" "Basic" "Discretionary" and "Travel" --- nevertheless, using  the Pivot Table, I can see quickly and neatly how much income of the ten different types, and how expenses fall into each of the 140 buckets, all nicely summarized in terms of Fixed, Basic , etc.

It may sound overwhelming, but once the structure is established it easy to maintain. It's a matter of getting the data from the financial institutions on a timely basis and doing what you need to do to apply the various budget category labels to each line.

Mathetes – I’ve been around long enough to have used Lotus 123 and WordStar.  And I’ve know people at work that were big pivot table users, but I never had a need (at work, at least) to get any good at them.  But I used them over the last five years for monthly treasury reports when I was treasurer of a non-profit, so I’ll start developing a new workbook for my monthly budget tracking using a database and pivot tables.

Question – do you change the expense numbers to ABS* -1 to change expenses that download as positive numbers to negatives?

NikolinoDE – I applied your formula to my workbook and it worked great.  Thanks!

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.