I have an MS-Excel chart, the data looks like this:
I want to produce a line-chart from this data.
The Y-axis (horizontal) will be the date range. The dates will span much more time than is shown in this little snip - it will be several years' worth of data.
On the X-axis, I would like to chart subsets of the rows, one for each person (Matt, Jim, Bob, etc). The result is like this:
In fact that is the actual chart I want. I'm able to produce this chart, obviously, but the way I'm doing it is fairly labor- and click-intensive. Here's how I do it: I use the data filter "tool" in Excel to filter the source data table on "person", then cut/paste that filtered data into a new sheet. I do that once for each person (4 or 5 people currently). Then I create a chart, and add the 4 or 5 ranges from all those new sheets into the chart. Ba-da-boom.
But every time I add new rows to the raw source data, I have to go through the cut/paste again, 4 or 5 times, then fiddle with the data range settings on the chart (expanding them all by one cell).
How can I update the chart more simply? Ideally I just want the chart to magically update when I add a new set of rows to the raw source data.
Is there a way for me to automate the data extraction? Is there a way to use named data ranges to make it simpler? Should I de-normalize the data or something?
I have no problem writing VBA if that is what it takes, but I'd like to minimize the effort involved.
Thanks for any suggestions.
(using MS Excel 2010 "Starter Edition" - it came with the PC)
From reading various webpages, apparently developer features have been disabled in Office Starter Edition. This includes automation as well as VBA-macros. So, I guess that option is out, for me. Anyone with further info, I'd appreciate it.