Budget Tracking Charts

Hello everyone. I'm trying to accomplish something that I feel should be fairly simple, but I have very little experience with charts in Excel. I've attached an Excel file that is a basic layout of what my actual workbook looks like. I can't get to the actual one because it's at work, and we can't post to forums there >.<. In any case, it's the same concept. I've also attached two pictures that show the most basic concept of how I would like the charts to display the data. Okay, here's what I'm trying to do:

We track each and every transaction throughout the year and categorize it based on the type of expenditure (travel, office supplies, etc). Each category has it's own sheet within the workbook. The transactions have a date in the far left, displayed in DD MMM YY format. In a separate sheet, I want to be able to add together all charges based on what month they occurred in. Once all of that is plugged in, I want it to reflect on two charts. The first will show each month on the X-axis and the $$ on the Y-axis. Also on this chart will be a line representing the $$ budgeted for that month for easy comparison. The second chart is a little simpler. It will just have a line representing the total quarterly budget and a bar that represents the total expenditures for that quarter.

Thank you in advance for any help offered.
Budget.xlsx

Qtrly_Chart.png

Qtrly_Summary.png

I have put in some info to get you going. Hopefully you can improve up on it by experimentation.
Suggested improvements.
(1) Put all your transaction data in a table. This will make all formula association dynamic.
(2) Add a Calendar tab (this is hard to convince people to do) with all your calendar information and formulas(ie each day of the year on separate row) and use it as a look table.
(3) So the helper columns in the transaction data will be look up formulas instead of calendar formulas.

mgd0903,

Thank you so much for that information. I guess I was trying to make it a little too complex when all I need to do is add columns that serve the purpose of simplifying the formulas. Can you explain a little more how placing all data in a table is beneficial? By dynamic formula association, do you mean that new entries into the table will inherit existing formulas?

The other way round, existing formulas that refer to your transaction data will inherit new entries. (When you convert your transaction data to a table)

Okay, I almost have the perfect product but I ran across an issue. In the cell that adds the transactions according to the month (Column K in the "Charts" sheet that mgd0903 provided), I need it to update when I filter the source sheet ("Supplies" in this case). Here's what I currently have in there:

=SUMPRODUCT(--(Supplies!$B:$B=J2),SUBTOTAL(109,OFFSET(Supplies!$I:$I, ROW(Supplies!$I:$I)-MIN(ROW(Supplies!$I:$I)),0,1)))

The formula does what I want it to (matches the month and adds the total $ amount, and updates with filters), but it slows down my workbook dramatically, even if I try to make an entry that doesn't affect the range. Is there a better solution?

(1) Covert your transaction data to a table. This will make all formula association dynamic. - Follow this advice. It is based on experience. Then let me know.
So all your references will be table references. The formula should still work, but much faster.

I actually had already taken your advice...the data is in a table. Still unbearably slow.

Okay... I just realized that I wasn't referencing the tables, but the entire column. Changed the references to Table[Column].

Correct. The formula should still work. Just try it.

Budget.JPG

Budget2.JPG