I'm using Excel as a fancy checkbook and I'm almost finished with the design, but there is one thing I'm having problems with.

I keep track of all my spendings and earnings by creating a sheet for each month's transactions (so 12 sheets total). I use a table with fields like Date, Category, Amount and some other irrelevant ones. In each sheet I created a pivot table that summarizes the spendings and earnings per category (categories can be: car insurance, food, misc., rent etc.) and shows a total.

Now I'm at the year summary, where I want to show all the categories I've used in the months and their monthly total. I was thinking I could put all the categories as column headers in one row, and the months in row headers in one column.

However, I'm having a hard time fetching all the categories from all the pivot tables. I want to keep them variable, so if in October I suddenly have medical expenses due to an illness or whatever, I can just add a row in October's table, which the pivot table for October will automatically pick up. The yearly summary should add this category then, too.

Any ideas on how to get this working efficiently ? Right now, my best solution is creating one big list in a sheet, grabbing all the categories from the pivot tables and thus having doubles (rent, for example, is an expense I'll have every month, so that will show up twice in that list). From that list I can filter all the unique categories I used and put that in the year summary. However, it's not really efficient and I was hoping there's a better solution.

VBA is okay with me, I have programmer experience (but not in VBA/Excel, so I don't know how much is possible).

Anybody have any ideas on this ?

PS: I hope I described my problem clearly, if not I would gladly upload a file that should clear things up.