In the Categories sheet I’ve added a monthly budget amount for one category of $750. In the yearly budget sheet, however, there are a few months that reflect wildly different numbers (e.g., $28,000 for a single month).
I zeroed out the category, but that just reduces those odd months by $750, it doesn’t take them to 0. Any ideas on why this is happening? Thanks.
Hi @RobBerger. Welcome to the community!
What a puzzle! Is it possible that you have some hidden columns (months) in your categories sheet, the numbers of which are impacting the annual roll-up? Can you confirm that the monthly numbers for the category on the Category Sheet match, all twelve, the monthly numbers on the Yearly Budget sheet?
Sometimes when I have had similar experiences, I have made a data entry error but can’t readily see it if the months are hidden.
Thanks for the reply. I’ve double checked, and there are no hidden columns.
I think @Brad.warren is right that the issue may be in the Categories sheet but I’d start by restoring the Yearly Budget template in case that got messed up somehow.
Another option to zero in on the issue is to debug the part of the Yearly Budget formula that does the budget lookup:
=VLOOKUP("Groceries",{INDIRECT("Categories!$"&$AS$3&"$2:$"&$AS$3),offset(Categories!$A$1:$A,1,match(Date(2021,11,1),Categories!$1:$1,0)-1)},2,FALSE)
You’ll need to temporarily insert this somewhere into the Yearly Budget sheet since it references cell lookups.
You can see that I’ve hardcoded “Groceries” and “11/1/2021” into the lookup.