I have a data table that looks similar to this:
Project - Start Date - End Date - Type of project - Region
1 - 1/1/2009 - 1/7/2009 -A - 1
2 - 1/1/2009 - 1/14/2009 - B - 2
3 - 1/8/2009 - 1/21/2009 - A - 3
4 - 1/8/2009 - 1/28/2009 - B - 1
5 - 1/15/2009 - 1/28/2009 - A - 2
I need to chart active projects by week. So, to spell out what I need to chart for the above data:
In week 1 (1/1-1/7), we have 1 active project, 1 Type A project
In week 2 (1/8-1/14), we have 3 active projects; 1 Type A, 2 Type B projects
In week 3 (1/15-1/21), we have 3 active projects; 2 Type A, 1 Type B projects
In week 4 (1/22-1/28), we have 2 active projects, 1 Type A, 1 Type B projects
I would need four separate charts - One for "All", and one for each region.
For each regional chart, I would like to have a column chart, with each column representing a date range (Week 1, Week 2). Each bar in the column chart would be stacked, saying that, for example, Week 2 would show a stacked chart with 1 Type A and 2 Type B in the bar. Here's a dummy chart that I did the hard way, as it were:
http://farm4.static.flickr.com/3420/...f092bf88_o.jpg
So basically, in a nutshell, how would I do a chart like that in a way that was much easier than the 15 minutes it took to generate this one with only five lines? The only way I have though of to do this would be to do an If-Then formula saying whether a particular line was in a date range, then copy those, paste those, and add a row saying "Week". Then I'd go back and do that for the other 12. (My boss wants a 12-week rolling chart.) I'd use the "Week" row to do the pivot. But that would take a LOOOONG time.
Any ideas?
Bookmarks