Thanks for this sheet. I love the concept, and it works great for Categories for me. However, when I try to aggregate by Group, it doesn’t display anything. Cell O8 shows “# N/A” which leads me to believe the formula doesn’t like how I’ve created my Groups.
I use a numerical prefix for my Groups to sort them in the order I like in most of the reports, not alphabetical.
Suggestions?
Hi @lallred,
I think your Group method is probably fine. But you have discovered an error in our sheet that happened during an update.
If you change the Sort By option to something other than Favorability, does the #N/A is Cell O8 go away?
In cell O8, the formula needs to be adjusted to change the word Difference to Favorability. We will make this update to the template. Thanks!
Let us know if this works now for you.
Jon
This is a great sheet - it is helping us focus on the categories we are overspending in.
The Income Actuals chart graphic (Col D near the top) appears to be including income from Categories I have marked as hidden. The hidden categories are NOT appearing in the detail list below so that part is working correctly.
The Yearly Insights sheet (Col A) shows me a different number for Income and that one summarizes only non-hidden Income categories like I would expect to see.
Thank you!
Hi @martha.rudkin,
Thanks for pointing out this issue. I see the problem.
We’ll have an updated sheet with this fixed next week.
Jon
Hi @martha.rudkin,
The Year-To-Date sheet template has been updated to respect and ignore transactions from hidden categories in the Summary row at the top. D6 thru G6.
You can get the updated sheet using the Tiller Labs Add-on and visiting the Manage Solutions page. Update to version 1.03.
Thanks for alerting us to this and hope you continue to enjoy using this sheet.
Jon
I’ve installed the updated sheet and it looks good.
Thanks for updating it.
Martha
I just tried to install sheet and I’m getting a #REF error D2, E2, F2, G3, and G3. The error displayed is “Function INDIRECT parameter 1 value is ‘Categories!]2:AG2’. It is not a valid cell/range reference.”
I was able to trace the problem to the formula in K16 that was set to “=char(64+J15)”. Simply copying and pasting the formula from K17 fixed the issue.
Just FYI 
One more quick note here. I didn’t like that the YTD numbers look funny at the start of a month since the entire month’s budget is included, but non of the actuals. If you are like me and you just want to see months that are “closed” you just need to update the following 2 cells:
I15: “=if(J14<year(today()), date(J14,12,31), date(year(TODAY()),month(today())-1,1))”
O5: “=if(J14<year(today()), TEXT(I16,“yyyy-mm-dd”), TEXT(EOMONTH(TODAY(),-1),“yyyy-mm-dd”))”
Basically it just forces I15 to first day of the previous month and O5 to the last day of the previous month. I may at some point try to add a “Include current month” drop down but this gets the job done for now
Hi guys - the YTD template should calculate entries as of the end of most recent month. Right now, it switched to May on the 2nd, it’s crazy, the compares are against a whole month more of budget. Probably best to only compare APril to April until something like the last few days of may or even the very last day of may, and only then switch to may (while date is in june) etc
Great suggestion, @alxpopa. Just implemented this. Restore the sheet and you should see a new dropdown that will allow you to refine the period date range.
Cheers,
Randy
Hi @charles.folsom,
A new version of the YTD Comparison sheet fixes the #REF error, which could occur if you have more than 26 columns on your Categories sheet.
As @randy mentioned above, there is now a dropdown option to include completed months or go thru the present month. At the beginning of a month, you may not want to include the current months budget. But at the end of the month, you likely do want to include it. This dropdown selector gives you the option to see it both ways.
Using the Tiller Labs Add-on, you can update the Year to Date sheet to version 1.04.
Jon
I guess my suggestion was to compare past months to expenses incurred through the past month, otherwise things get really out of whack
Thanks for today’s update to the sheet.
I am seeing a blank YTD budget number for my category called Federal Income Tax. It is line item #6 in my Categories sheet. No matter how I sort the YTD sheet, the budget amount
stays blank for this one category. The Actual number for the category is calculating correctly.
I normally sort the sheet by Category, Favorability, A-Z.
(I have a custom version of this sheet that uses all transactions for the current month and it is still working correctly this morning).
@selina found a missing formula in the hidden area. I just posted an update. Latest version is now 1.05.
@martha.rudkin, could you restore the YTD template in your spreadsheet and see if the new version addresses the blank budget cell?