Dynamic graph showing last x months of financial data

[bujaman]

I have a spreadsheet that tracks my small business income and expenses. I have programmed a dynamic graph that charts this information, but I would like to make it more useful by being able to define the amount of months shown by changing the value in D8. Is this possible?

 

[wigi]

So, you have already a function like OFFSET I presume? (since you are talking about a dynamic chart)

 

[wigi]

See also:

http://www.mrexcel.com/forum/showthread.php?t=571993

 

[Jon Peltier]

In the following tutorial I show how to make a dynamic chart that graphs the last 12 months. You can easily modify it to cover the last N periods:

Chart the Last 12 Months

 

[bujaman]

Yeah, i am already using offset. Here is what I have so far for named ranges with the offset command:

=OFFSET(Income!$A$2,,,COUNT(Income!$A$2:$A$200)) - Dates
=OFFSET(Expenses!$E$2,,,COUNT(Expenses!$E$2:$E$200)) - Expenses
=OFFSET(Income!$E$2,,,COUNT(Income!$E$2:$E$200)) - Income

 

[bujaman]

Jon,
Thanks for the reply. I have seen that tutorial and I have gotten it to work, but the problem is is that my data is structured differently. I don't have costs or expenses totaled for each month, but as I spend money on an item, I enter it into the spreadsheet, so some months there are 20 entries, and others there are 5, it just depends.

 

[bujaman]

You can see what I have in the file linked here.