Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Budget worksheet: summary of various transactions

Copper Contributor

I'll try my best to the describe my problem here. 


I'm creating a budget worksheet. I have created a table for transactions, with drop down list for both categories and subcategories, there will be different (but identical) sheets for each month. The subcategories are dependent of the categories (eg. if I choose "Income" I'll get "Salary" & "Sales" as options but not "Insurance"). 


In the worksheet I want to summarize all the categories and their subcategories based of the transactions. I have created tables for each category, it's here my problem starts. 


I created a Pivot table of the transactions, which I base the summary-tables on using "Get.Pivotdata"



I already know that some subcategories won't be used every month, which means it'll be missing in the Pivottable, which then will create an error "#REF!" in the summary <-- See picture.


So my question is: Is there any possibilities to create a formula that writes 0 instead of "#REF!"? Would the =IF_ERROR work? If yes, how should I write it? Or do you have any other recommendations?

I want to keep the worksheet as dynamic as possible, so the only changes should be made in the front page and affect changes to the rest of the sheets.  

I hope my description makes sense and that some of you have any ideas for me to solve my problem!



1 Reply



May I challenge your design, before you get too far into using it?


I have been working on my own budget worksheet (actually more of an income and expense tracker) recently, and would commend to you the basic design approach I've used.  I've had it going since 2019, all the way through last year, and will be continuing in 2024.


There are only three sheets in the entire workbook.

  1. Transaction History: this contains every transaction, income and expense, and it's where I add to each line the various budget categories and sub-categories. All transactions going back to 2019 are in this single sheet. All checking accounts and all credit cards. All in one ongoing transactional database.
  2. Summary: A single Pivot Table. I can filter it (and generally do) so it's only showing one year at a time, but it shows every category and subcategory by month. Perhaps because of the way I'm doing this, I don't have the problem you refer to. If in any given month, a sub-category isn't used, it isn't used; no error message, just a blank area. If a sub-category that was used in 2020 isn't used in 2021, then it appears in 2020 but doesn't even appear in 2021.
  3. CatTables: this is where I have my tables of budget categories and sub-categories, which are used as the source for DataValidation back in the Transaction History sheet.

My basic point: by having separate sheets for each month, you may be doing more of the work yourself--separating things  by month--and failing in that way to let Excel's pivot table tool do the work of that separation and reporting. When we keep records on paper, it makes sense to create that kind of order, but in moving things to Excel, it makes sense to let Excel take care of creating monthly summaries, if needed, quarterly, etc., and doing so from a single ongoing--for as long as you keep your accounts running--database.