Docs: Monthly Analysis worksheet for Excel

Microsoft Excel Show & Tell
, ,
46

Yep, that worked, but then it messed up the 2023 data. I guess I just have to save separate files for each year?

47

If you follow the steps in the help article you should be able to preserve access to previous budgets and also add new years.

48

Hi Randy - the Monthly Analysis sheet was working great for me. However, after saving a version the sheet went to #value for all months. Any thoughts on how to fix?

Thanks,
Ben

49

Hi, When using Monthly Analysis and selecting a Date Range from B2, I get a drop-down list that starts with “Past 12 Months”, then includes every year from 1900 down to 2024.
Don’t know if this was intended. Also, I am wondering if in the Date Range options if there’s a way to compare only current month to last month. or current month to same month last year?

Thanks

50

The intent is just to show the data for the date ranges available in the dropdown and what you see when you select it is what you get as far as comparisons. It’s a month over month comparison for the adjacent months based on the selected date range only.

51

Markv6 - not sure if this helps but I had something similar (Dates back to 1900) when I pasted older Mint transactions into the Transactions Sheet. I screwed up the range and it included blanks which gave me what you were seeing dates back to 1900. I went into Table Design and reset the Transactions to only include valid rows. That fixed the monthly analysis date range for me.

52

Thanks for the update @bburwell

54

@randy, I just downloaded the workbook. The filename reflects v1.01 231027, but I see v1.02 when I open it.

Thanks for your hard work.

55

Thanks for the feedback @PistolePete !

56

It’s been a while since I had a look at this, @PistolePete, but it looks like the last version was 1.01 (like the filename, not the header). I just updated the header. Thanks for the note.

57

David,

First, I love the output and the dynamics of this sheet!!

I have gone around and around with ChatGPT trying to replace the formulas that require iterative calculations to be turned on.
I believe I have isolated them to “Group Index”, “Cat in Group”, and “Cat line” in the helper area of the worksheet.

I have many reports and a ton of transactions in my workbook. The iterative calculations from "monthly analysis’, from the ‘Scans’ formula has almost made the workbook unmanageable. Disabling automatic calculations has helped but it not optimal.

Do you know of a non-iterative formula to replace the ‘Scans’ or do you have an alternative method without any iterative formulas?

58

Hi @aalcorn perhaps this recent Community solution can also help with some aspects of performance? Adding a "kill switch" to stop reports from recalculating while filling transactions