Yearly chart with automatic data and subtotals
 

Hi, hopefully this question makes sense!

I have a spreadsheet that updates the number of sales for 11 businesses each
month (showing the entire fiscal year at one glance), based on the numbers
given in other spreadsheets (sorted by month). I have my formula set up so
that the spreadsheet will prompt to update upon open. However, not all
spreadsheets are created yet (like July 2008 for example, although I do have
the July column formulae written to pull from that spreadsheet because it
will eventually be created) so having static data for my chart isn't working.
I selected the cells from January to December for the chart because my boss
(not so Excel-savvy) likes to view the spreadsheet from time to time and it
needs to be up-to-date whenever he opens it.

The chart (stacked column or line) needs to keep the monthly values separate
(and also display groups by quarter), but also display the total to date.
The problem I am having is when I do the stacked line chart, anything showing
as blank will cause the line to go to 0 so it looks like a big drop off. The
stacked column chart has more problems: I can't set it up so that each
community has its own column but each column is sorted by quarter and then by
month (a feature I can do when I use the stacked line). Also for whatever
reason, the data legend is upside down - January on the bottom, December on
the top.

As I said, I really hope that made enough sense. What I need is either a
formula to create the stacked line chart but not include the blank values, or
the stacked column chart with the legend reversed and separation by quarter.
For both charts I need a total-to-date.

Thanks in advance!