Hello, I am running Excel 2007 and trying to make a chart that will add a new series automatically as new data is entered. I haven't seen this particular problem discussed in the forums, but if it has please send me a link: this is driving me nuts!
The problem:
Here is a simplified version of my data
<table style="border-collapse: collapse; width: 300pt;" width="396" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <col style="width: 50pt;" span="5" width="66"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl67" style="height: 15.75pt; width: 50pt;" width="66" height="21">
</td> <td class="xl68" style="width: 50pt;" width="66">SeriesA</td> <td class="xl68" style="width: 50pt;" width="66">SeriesB</td> <td class="xl68" style="width: 50pt;" width="66">SeriesC</td> <td class="xl68" style="width: 50pt;" width="66">SeriesD</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Mon</td> <td class="xl70">7</td> <td class="xl70">9</td> <td class="xl70">8</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Tue</td> <td class="xl70">5</td> <td class="xl70">4</td> <td class="xl70">1</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Wed</td> <td class="xl70">6</td> <td class="xl70">5</td> <td class="xl70">1</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Thu</td> <td class="xl70">4</td> <td class="xl70">4</td> <td class="xl70">3</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Fri</td> <td class="xl70">5</td> <td class="xl70">3</td> <td class="xl70">7</td> <td class="xl70">
</td> </tr> </tbody></table>
I have created a line chart to plot these four series (Mon-Fri as the horizontal category labels). As you can see, SeriesD does not have any data yet, and so I would like to exclude it from my chart (both as a line and as a Series in the legend). Over time I am going to be adding more and more series to this chart (SeriesE, SeriesF, etc.), and I would like Excel to update the chart as the data is added, but exclude these series entirely when they are blank.
This spreadsheet is going to be used by Excel novices who will not be able to run macros, reset filters, manually hide blank rows, or modify the chart in any way. They will only be able to add new Series data, so the fix needs to be entirely automated up front before I send it out.
What I've tried:
I first thought to use a dynamic name as the Chart Data Range, which works the first time, but does not update itself over time. Apparently, the Chart Data Range converts any defined names into their actual references when you close the Select Data menu. This would seem to be a perfect solution, if only the defined name would stay dynamic in the Select Data menu. I'm hoping that I've just overlooked an option here, and somebody knows how to do this.
Any help or suggestions or simple solidarity with this frustrating problem are very much appriciated! Thank you all in advance!
The problem:
Here is a simplified version of my data
<table style="border-collapse: collapse; width: 300pt;" width="396" border="0" cellpadding="0" cellspacing="0"><col style="width: 50pt;" width="66"> <col style="width: 50pt;" span="5" width="66"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl67" style="height: 15.75pt; width: 50pt;" width="66" height="21">
</td> <td class="xl68" style="width: 50pt;" width="66">SeriesA</td> <td class="xl68" style="width: 50pt;" width="66">SeriesB</td> <td class="xl68" style="width: 50pt;" width="66">SeriesC</td> <td class="xl68" style="width: 50pt;" width="66">SeriesD</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Mon</td> <td class="xl70">7</td> <td class="xl70">9</td> <td class="xl70">8</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Tue</td> <td class="xl70">5</td> <td class="xl70">4</td> <td class="xl70">1</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Wed</td> <td class="xl70">6</td> <td class="xl70">5</td> <td class="xl70">1</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Thu</td> <td class="xl70">4</td> <td class="xl70">4</td> <td class="xl70">3</td> <td class="xl70">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl69" style="height: 15pt;" height="20">Fri</td> <td class="xl70">5</td> <td class="xl70">3</td> <td class="xl70">7</td> <td class="xl70">
</td> </tr> </tbody></table>
I have created a line chart to plot these four series (Mon-Fri as the horizontal category labels). As you can see, SeriesD does not have any data yet, and so I would like to exclude it from my chart (both as a line and as a Series in the legend). Over time I am going to be adding more and more series to this chart (SeriesE, SeriesF, etc.), and I would like Excel to update the chart as the data is added, but exclude these series entirely when they are blank.
This spreadsheet is going to be used by Excel novices who will not be able to run macros, reset filters, manually hide blank rows, or modify the chart in any way. They will only be able to add new Series data, so the fix needs to be entirely automated up front before I send it out.
What I've tried:
I first thought to use a dynamic name as the Chart Data Range, which works the first time, but does not update itself over time. Apparently, the Chart Data Range converts any defined names into their actual references when you close the Select Data menu. This would seem to be a perfect solution, if only the defined name would stay dynamic in the Select Data menu. I'm hoping that I've just overlooked an option here, and somebody knows how to do this.
Any help or suggestions or simple solidarity with this frustrating problem are very much appriciated! Thank you all in advance!