I tried to reproduce this with 32+ categories and a dozen groups and I didn’t see it in my worksheet. Are you saying that you added a 32nd category to your Categories sheet?
Typically this error is related to a spill-range formula needing to fill down into cells that already have hardcoded values— essentially blocking the formula from filling to its full range. I’d check if there is a cell with some manually-entered data stopping a formula above from filling (the problem may be in the hidden area to the right).
This looks wonderful, however, I’m not seeing any data being populated in the table with the 12 months. If I choose Groceries as the Category for example from 2023 (I have data for 2023 Groceries and have a budget set), all 12 months appear with $0.00. I have linked to my local copy so that data link is correct. Any idea why this isn’t working?
=SORT(
CHOOSE({1,2,3},
FILTER(Categories[Type],(Categories[Type]<>“”)(Categories[Type]<>“Transfer”)(Categories[Hide From Reports]<>“hide”)),
FILTER(Categories[Group],(Categories[Group]<>“”)(Categories[Type]<>“Transfer”)(Categories[Hide From Reports]<>“hide”)),
FILTER(Categories[Category],(Categories[Category]<>“”)(Categories[Type]<>“Transfer”)(Categories[Hide From Reports]<>“hide”))),
{1,2,3},{-1,1,1})
That looks, right, @mu3484343. Are other sheets (like the Monthly Budget) populating with data? Does it look like the hidden area to the right of Z7 is populating with data that isn’t making it to the dashboard?
Budget data appears to be showing in the primary table (not the hidden ones) as well as the hidden ones. I’m not seeing the Actual spending show up in the data. I am able to see the Actual spending on all of the other templates I have (Spending Trends, Monthly Budget, etc).
FYI, figured this out. I imported all of my data from Mint and the import didn’t include data in the Month and Week columns on the Transactions tab. Got that data added in and it all works as intended. This is really nice. Thank you!
I believe someone asked this above but I didn’t quite understand the answer - is there a way to adjust the 12 month range to custom months (e.g. 03/2022 - 03/2023) instead of just using a year?
Regarding your question of changing the range, the periods are created in cells P2:P13 in the hidden area. You could either rework the formulas or hardcode the values there for periods that don’t start on the first of the year.
Very helpful. I just ended up editing the negative number in the A12 formula (,SEQUENCE(12,1,-12,1)) and I can get a custom 12 month range of my choosing. Thank you.
@randy thank you for making Monthly Analysis available for Excel. I posted about an error message I was getting with the initial installation, but I was able to easily resolve it.
I’m not quite sure TBH, @nwb, since I can’t see your source data (transactions and categories) or the formulas in your template. I can see that the template master hasn’t changed since 4/2/2021. Have you restored to the last version?
If I were trying to debug, I’d unhide the columns at right (particularly BB:BM and AM:AX) and try to see in the source data where the values reverse polarity and then dig into the formulas to figure out why. Maybe it has to do with your transaction data? Maybe it has to do with your category types? Maybe it has to do with the template being changed from the original master?
I’m not sure if there is more than one issue, but I think, from your last post, the issue is that you need to build a new year of budget columns in the category sheet. Have a look at this then we can see if there are more issues.
