Monthly Analysis Worksheet Bug? (Excel)

I think I found a bug? I’m not seeing any error messages or having issues elsewhere. I can’t get the Monthly Analysis worksheet to update for previous months. The appear to be frozen once the month has passed. I am viewing the ACTUAL amount, but the data isn’t reflected in AVAILABLE either.

My transaction for my February home security payment is not appearing correctly in the Monthly Analysis worksheet for Excel properly. The transaction DOES appear in Spending Trends and Monthly Budget correctly. The dates are correct, the entire transaction line looks just like the other lines in the same category, and I can’t figure out what is different about it. I duplicated the one line, and the other sheets accounted for the additional transaction. Monthly Analysis did not update.

The same thing has occurred for at least two other categories, so this seems to be throughout the worksheet. I added another iteration of the newest release of this worksheet, and the problem occurred in the newest version also. Duplicating the one February transaction or adding a new bogus transaction did not update in the Monthly Analysis worksheet.

Am I misunderstanding the purpose of this worksheet, or should it be updating if new data is added after the end of a month?

Thank you for any insight and assistance.

@jaggy732 just want to confirm that you did “update reference links” step as noted in the help article below after you moved the Monthly Analysis sheet into your workbook?

If the reference links are still pointing to the old source then the template won’t update with your actuals.

1 Like

After FAR too much squinting and banging my head against Excel’s walls, I think I figured it out:

It appears that, for whatever reason, the Monthly Analysis worksheet pulls the dates from the “Month” column (H) on the Transactions worksheet rather than the “Date” column (A). The date in the Month column must be the first DAY of the month (Jan 1, Feb 1, etc.), not merely any date within the month.

When I’ve been updating a transaction, I’ve only been changing the date column. Every other worksheet has handled this appropriately. Seems weird to have one worksheet not operate like the rest, but I think I can make it work from here on out.

In case anyone ever needs to know how I checked column H against column A to see if they were within the same month, I set up a conditional format:
formula: =IF(MONTH($H1)=MONTH($A1),0,1)
apply to =$H:$H
format to highlight or change the text color as desired.

I’d VERY much prefer that the Monthly Analysis worksheet pulls data from the date column like all of the other worksheets, but I’m not quite adept enough to fix this without likely breaking the entire workbook. Maybe someday someone will fix this inconsistency.

That’s some good sleuthing, @jaggy732. You are right that we often don’t use the Month column and that it can sometimes be unreliably populated… but, I suppose in this case it was a shortcut to what was needed.

I think the fix is pretty straightforward…

In the cells that calculate the actuals AD7:AO7 you can replace the reference to the month as the date as a dynamically created first-of-the-month date by replacing this:
Transactions[[Month]:[Month]]
with this:
DATE(YEAR(Transactions[[Date]:[Date]]),MONTH(Transactions[[Date]:[Date]]),1)

Let me know if that does the job for you.

1 Like