OK, So I've spent nearly the entire day looking through countless forums to see if anyone has had this question answered and could not find anything on this. Hopefully someone here can help.

I have a monthly budget. I input my purchases into a sheet called "Checking Ledger", then pivot table data is organized in another worksheet called "Checking Totals", and then the data is grouped by category and added together on the main page. So it kinda looks like this. So all purchases made at Safeway, Whole Foods, & GG Market are put under "Groceries" is added up and the total updates in the "Food" Section on the main worksheet. It looks something like this:

-Groceries

Whole Foods $5.75
12/3/2014 $2.75
12/10/2014 $3.00

Safeway $20.00
12/10/2014 $10.00
12/30/2014 $10.00

The formula I've been using is:

=GETPIVOTDATA("Sum of Payments",'RCU Checking Totals'!$A$3,"Expense Type","Groceries")

That has worked perfectly. BUT, every month I have to erase everything in the ledger for the next month. This makes me have a separate workbook for January, February, March, and so on. The reason I do this is because I'm trying to get my finances in order and want to see how much I spend on a particular thing in a particular month. That way I can predict how much I'll be spending in the future.



What I would like to do is have one workbook for the year, and change the formulas so that the main page will only get the items for a particular month. I can't seem to figure it out. Can anyone help me?

How about keeping all the data in one sheet (be a lot easier) and filter the pivot by the dates you want? I think that can be easily done though admittedly I don't use pivot tables much. Let me see if I can work something up for us to look at.

See how this looks for you. I've grouped the data by month and year. To do that, you right click on any of the date values (so for here, you would right click either Jan, Feb, or Mar in row 3) in the pivot table and select Group. You can also filter by a specific month.

Attached Files

BudgetPivot.xlsx (16.6 KB, 9 views)