Forum Discussion
I need to subtotal similar items in a checkbook spreadsheet monthly by category
You write: I need to build a new spreadsheet within the workbook to display these subtotals for each category(the number of categories doesn't change) for each month.
And I first want to challenge that "need," by suggesting that what you really need is to display the subtotals for each category for each month. That is to say, whether or not a "new spreadsheet within the workbook" is what you need is questionable. Are you aware of the Pivot Table? Since your database already identifies months (dates) and categories, the Pivot Table would readily grant you your subtotals, with the ability to identify all of the backup details as needed. And a Pivot Table would work without new formulas, could be refreshed at will.....
Here's a good resource to learn about PivotTables.
If you want more guidance, might I suggest that you post a copy of your current workbook, either on OneDrive or GoogleDrive, posting here a link that grants access to it.
mathetes Check out my revised workbook. I now have columns in my data for payee, sub-category, and category. This is more of what I want to display on the report. I have added two payees that don't appear with payments at the top of the data so that they will be displayed with $0 values in the report. If I do this for all 33 of my possible payees they will all appear in the report as a sub-cat and cat item.
Question: If I want to add a column to the right of the Dec column in the report showing Monthly budget for a sub- category item, how would i do that? Same question for yearly budget to the right of the grand total?
Thanks for your help so far.
Here's a revised version that enables you to add (dynamically) new sub-categories and categories, automatically updating the data-validation for the column in your checkbook where sub-cats are entered, and automatically looking up and entering the category based on that prior entry.
Let me know if the quick and dirty way I incorporated the budget figures is altogether inadequate. I'll see what I can do, but I'm not optimistic.