Sorry I have been completely MIA around here for months. Work has been crazy and I’ve been working on a little (well, it grew a bit) project.
The Savings Budget community template met about 98% of my needs, but how I had adapted it involved basically shoving some stuff in the margins, and that ended up breaking a few things. I wanted to basically rebuild it from scratch, but also keep it backwards compatible with the existing Savings Budget community solutions extension script, which was no small feat in and of itself. I replaced the old helper columns with some dynamic array formulas that took me a lot of time (and a lot of frustrating debugging) to figure out, but the whole thing should be faster and less resource heavy. Some of the key differences are that I needed a calculation of how much of my assets were allocated to my rollover budget “envelopes” each month, and I needed to be able to exclude some accounts that I didn’t want counted in those liquid assets.
It’s been a solid six months of research and work, but I think I finally have it ready enough for others to take a look at.
This redesign is designed to seamlessly replace the existing Savings Budget sheet and for the extension script to modify the Budget Journal and/or Categories sheet. It does rely on the Budget Journal sheet that’s created with installing the Savings Budget sheet.
Would anyone actually be interested in this? If this is something nobody would actually want, I won’t bother, and I also don’t want to offend the original Savings Budget creator for hijacking their community template and community solutions script (which are amazing!)
But if there’s interest in looking at this or testing, I’ll happily put it up as a template for people to add to their sheets for review and feedback.
Ok, let’s see if this works. Here’s a link to a copy of the sheet.
You should theoretically be able to copy it to your workbook, rename your existing Savings Budget sheet something like Savings Budget Old or something, and rename this to Savings Budget. If I didn’t screw anything up, it should work with the existing community template scripts.
If it doesn’t, please let me know so I can try to debug this!
Looks like a lot of work! I gave it a try, but I’ll need more time. The references to the columns in the other sheets are not dynamic (using indirect). Some of mine are not in the same order as yours so the sheets returns error and n/a messages. I’ll see what I can do
My accounts and balances transferred over, but everything else has the error code “No Matches are found in FILTER evaluation” and the cells show #N/A. It sounds like it’s probably what @PCB found.
Let me know what errors show up and I’ll see if I can fix them. There were a few things I had to use indirect references for early on because I couldn’t get some stuff to work, but I’ve learned a LOT about array formulas in the last 6 months, so some of it might be things I can finally debug away.
Is the error showing up for you starting in B12? Or is it showing up somewhere else? B12 is my biggest dynamic formula that’s given me headaches figuring things out.
I’m working right now on locating any older indirect references; I’d just copied the References table over from the old Savings Budget and hadn’t messed with it much because I was afraid of breaking it, but I think I understand it well enough to replace those indirect lookups with something more flexible.
Ok, yeah, I think I see at least one of the issues. Q tracks back to P, which I had mostly copied over and modified lightly because it’s where the app script in the extensions tracks to, and there were some old indirect formulas in there that I didn’t account for. I’ll work on that and see if that helps anything. B12 might be partially a culprit here as well; it doesn’t have any indirect lookups, but I think there’s a spot or two where I still have it assuming that Categories columns are in the same order Tiller put them in originally and I think I can fix that now.
I had AI take a look at it and it fixed a few problems, and made it work. It is a nice step up to true Envelope budgeting. It’s working great with my heavily customized Tiller sheet. But, the formula in B12 is a monster, but it does work now. I don’t see how you could troubleshoot this formula without AI assistance.
It was a LOT of work and an entire notebook of notes and annotations, let me tell you. I’ve written and rewritten that more times than I can count over the last 6 months.
This improvement is exactly what the Savings Budget needed. Tiller is focusing on Holdings right now, so it’s nice to see Envelope Budgeting getting a boost! Thanks for your hard work.
Ok, I think I’ve repaired all of the Column P indirect references and fixed the B12 parts that were causing the reference problem if anyone’s willing to give this a try.
Thanks much for the beta testing, guys! I really sincerely appreciate it.
That cleared the errors. I like the concept of it, but I will have to try it starting at a new year because of how I do things with my rollovers. I have a bunch of negative numbers/incorrect numbers because I zero out on the first of the month for the previous month/year, so that was in there. I deleted all my Jan 1 adjustments, and the numbers are still incorrect when comparing it to both the original savings budget and my year to date budget doc. I might dig deeper into how you are doing things to see if I can find why the numbers don’t match. I definitely like a lot of the features you have and if I can’t figure this out for this year, I will definitely work on it for next year. Thanks!
I’ve written and rewritten that more times than I can count over the last 6 months.
I like the concept of it, but I will have to try it starting at a new year because of how I do things with my rollovers. I have a bunch of negative numbers/incorrect numbers because I zero out on the first of the month for the previous month/year, so that was in there. I deleted all my Jan 1 adjustments, and the numbers are still incorrect when comparing it to both the original savings budget and my year to date budget doc. I might dig deeper into how you are doing things to see if I can find why the numbers don’t match. I definitely like a lot of the features you have and if I can’t figure this out for this year, I will definitely work on it for next year. Thanks!