I have just installed the Budget Plan on my Tiller Google Sheets. I have figured most of it out but the âTotalâ boxes are not populating. For instance, in box M1 it just shows âTotal $0â. The formula in the box is = "Total " &. Any ideas on what I need to do to get the totals populated?
If you pull your formula bar window to be slightly taller youâll see the entire formula. Each of those cells is simply summing up the budget numbers below them, while factoring in if they are an âincomeâ or âexpenseâ category. If you donât see numbers in the grid from M through X, it may be that you donât have the budget items âEnabledâ (column A), you donât have a frequency set, or you donât have an amount set for frequencies that require it.
Thank you for your quick response. Due to your pointers I figured out that I had added âTagsâ to my categories at some point in time. This added an additional column between âGroupâ and âTypeâ in the Categories tab. By removing this column, the hidden C column in the Budget Plan populated and then the totals filled in.
This is a great solution that you have developed. Thank you!
1 Like
Yeah, looks like I hard-coded in looking to Categories A-C to find the category type. I have a Tags column as well, but itâs at D, which doesnât cause an issue. Maybe next version Iâll try to make that smarter so if someone has an extra column before C it will still work. Thanks for pointing it out, glad you got it working.
Edit: The fix is easy, just change C3 to:
={"Type"; ARRAYFORMULA( IFERROR( VLOOKUP( E4:E, {INDIRECT(AK2),INDIRECT(AK3)}, 2, FALSE ), IFERROR(1/0) ) ) }
Iâll look to add this to the next update, whenever that is.
1 Like
Iâm having the same issue using the external reference. After some work Iâve determined the indirect function used will not accept a reference with a sheet without adding quotations. in HLOOKUP(N$3, INDIRECT(notes), 2, FALSE) the value in notes needs to be split and reassembled with a leading â'â for indirect to work with a sheetname.
If I isolate just the hlookup function on another sheet where column A has the external reference, then this, while ugly, works: HLOOKUP(B$40, INDIRECT(â'â & left($A41, FIND(â!â, $A41)) & mid($A41, FIND(â!â, $A41)+1, 20)), 2, FALSE) but it results in a circular reference in the arrayformula.
Indirect does not resolve a sheet reference with just the â'â prepended, I had to split the text for it to work.
Any ideas how to get this working within the arrayformula?
I see the problem. Works fine if your sheet name doesnât contain a space, but with a space, which then requires the single quotes around the name, things break because Google Sheets wonât show the first quote. Iâll mull about on the best way to handle this, but for now, if you have a sheet name with a space in it, you can put a formula like the following in the notes column and it appears to work with the existing Budget Plan formula:
="'sheet name'!F10:Q11"
Thanks for pointing this out!
Thanks, I must have messed something up, when I either quote the reference (=ââBudget Plan Inputsâ!B1:M2â) as you did above or just remove the spaces in the sheet name (BudgetPlanInputs!B9:M10) all the budget columns continually recalculate.
I see the problem I am trying to calculate budget categories based other categories. For example, retirement contributions based on income. Once I paste the values rather than a reference it works.
It would be nice if there were a way to adjust categories budgets like this, but this is very workable as is.
Yea, you are referencing data from the Budget Plan sheet, which wonât work as it is a circular reference. The âExternal Referenceâ option is intended to pull data from a different sheet. You shouldnât need to reference other budget items, since all budget items in the same category will be sumâd up on the Categories sheet.
I was able to get mine to work with some work. This was my end result: =ââBillsâ!A9:L10â
1 Like
Joseph, this is extremely useful, many many thanks. I applied my budget a little differently than in your video. My wife and I each receive social security payments which I had previously lumped together in one Category. For the budget plan, rather than disable that combined item, I added a descriptor for one of us and than added another social security line immediately below with a descriptor for the other person. This keeps things together and avoids additional lines in the budget plan.
I thought it would be very helpful to have a quarterly and semi-annual distribution. Then I realized I can to that with weekly and a 13 and 26 multiplier.
My budget is a little different than some as I have significant annual expenditures and incomes from investments. I developed a budgeted and actual cash flow column chart from the yearly budget sheet and this helps me plan for these expenditures.
Many thanks for your excellent and hard work.
Mel
I tried to make it as flexible as I could to support special situations like yours. Glad itâs working for you!
Great idea for planning (and so powerful with the multipliers)
That said, for workflowâs sake (And I know it would be a project to adjust widely throughout tiller)âŚ
The Tiller Sheet Categories should be just about categories
(Need to remove the budget part)
Budget Plan should be just about budgeting (and would take over the budget part of the sheet category)
(add a drop down year so people can keep their historic data, remove the possibility for subcategories (as this is the job of the category sheet) and make it look nicer A la âSaving budgetâ with an area for income/expenses, groups etc.)
Saving budget needs to be able to change the budget numbers in the Budget Plan (and themselves, add totals in income vs expenses)
From there, we have a clean structure on which to build.
I will explore the ability to work with historic data. What do you mean by âsubcategoriesâ?
As far as making it more âSavings Budgetâ like, in the same way you want to separate Categories from Budget, I think Budget Planning needs to be separated from evaluating the results of that budgeting. My first attempt at that is Budget Status, an alternative to Savings Budget. Iâd love to get feedback on that template if youâd like to have a look. I agree it would be nice for Savings Budget to be able to update Budget Plan, but that would be something @randy would need to implement, which Iâm guessing isnât an easy thing to do.
I started playing with this sheet and reviewing the topic. I donât see actuals being reported against the budget. Am I missing something? Thanks!
It simplifies things down to show the end result of âhow much do I have availableâ. If you want to see the details, expand the sections (eg. the + above column N) and youâll get budget and current numbers.
Thanks for your super quick response - I appreciate it! I think Iâm missing something because I donât see anything suggesting an expanded view or more. Granted I havenât gone far at all in setting up the budget.
Oh, sorry, was thinking you were asking about Budget Status. Budget Plan is just about planning your budget, it doesnât do anything with actuals (beyond pulling them in from the past if you choose that option). To compare your budget with your actuals, youâd need to then use something like âMonthly Budgetâ, âSavings Budgetâ or âBudget Statusâ.
Are any of those integrated with Budget Plan - meaning I can view the budgeted vs actual per month?
Iâm familiar with Monthly Budget and I see Savings Budget, but I canât find âBudget Statusâ. Thanks
I found the Budget Status I think
Any budgeting tool should look to the Categories sheet for itâs budget info, which is why part of the setup for Budget Plan includes copying a special formula to the Categories sheet. You can fine Budget Status here.