Docs: Monthly Analysis worksheet for Excel

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).

Let me know what you find.

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?

Thank you.

To confirm the data is referencing transactions in your local workbook, can you unhide the cells to the right, click on Z7 and peek at the formula?

Do the references cell look like Categories[Type] or do they have prefixes to external files?

The formula I see for “Z7” is:

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”))),

Thank you for your help.

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?

Thanks again.

1 Like

@mu3484343 I had the same problem (after importing from Mint). Thank you for figuring this out!

1 Like

Thanks for the debug, @mu3484343.

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.

Well done, @mu3484343! The power of spreadsheets!

1 Like