Charts -- Hide blank series with dynamic names?

N

NemoYotta

New Member
Joined
Oct 27, 2008
Messages
2
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!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Best thing I could find was this.

http://peltiertech.com/WordPress/2008/07/03/dynamic-chart-source-data/

To make this work more dynamically, you can include the SetSourceData command in a Worksheet_Change event. Right click on the sheet tab, choose View Code, and the VB Editor will appear with a code module representing the active sheet. Put this procedure into the module:

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Me.Range("ChtSourceData")) Is Nothing Then
    Me.ChartObjects(1).Chart.SetSourceData _
        Source:=Me.Range("ChtSourceData"), _
        PlotBy:=xlColumns
  End If
End Sub

This procedure executes whenever there is a change to a cell on the worksheet (not a calculation, but a change in an entered value or formula). The If checks whether the change occurred within the dynamic range ChtSourceData; if so, it resets the chart’s source data range. It’s automagic.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
139
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
A
  • Locked
  • Question
values across tables found by name
Replies
2
Views
374
Fluff
Fluff
P
  • Question
Excel line chart to dynamically ignore series?
Replies
0
Views
332
povictory
P
Z
  • Question
Chart with Dynamic Ranges
2
Replies
11
Views
494
awoohaw
awoohaw

Forum statistics

Threads
1,214,528
Messages
6,120,065
Members
448,942
Latest member
sharmarick

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top