Dynamic number of series in a chart

[hstaubyn]

I have a dynamic named range which I am trying to use as the source data for a stacked bar chart. The named range covers a number of rows with data in them (in multiple columns).

Each row is currently shown on the graph as an individual series. In the first column of the named range are the names of each row's data (which translates to the chart as the series names in the legend), the remaining data is made up of numbers.

The number of rows included in the named range is variable depending on what year it is and also whether or not a new row is added.

I would like the chart to automatically pick up that either another row has been added to the named range, constituting another series, or a row has been taken off the named range, making that series disappear. I can't get the chart to use the named range I currently have.

Does anyone know of a non-VBA way of making this work?

Thanks,
H

 

[BiocideJ]

You can use Named Ranges, you just have to define them slightly differently than you would a normal Series range. Specifically you have to include the workbook name with them such as this.

Book1.xls!MySeries

Where Book1.xls is the SAVED workbook name and MySeries is the Named Range in that workbook.

You can read more here...
http://office.microsoft.com/en-us/e...eate-dynamic-charts-in-excel-HA001109801.aspx

 

[pgc01]

I have a dynamic named range which I am trying to use as the source data for a stacked bar chart. The named range covers a number of rows with data in them (in multiple columns).

Each row is currently shown on the graph as an individual series. In the first column of the named range are the names of each row's data (which translates to the chart as the series names in the legend), the remaining data is made up of numbers.

The number of rows included in the named range is variable depending on what year it is and also whether or not a new row is added.

I would like the chart to automatically pick up that either another row has been added to the named range, constituting another series, or a row has been taken off the named range, making that series disappear. I can't get the chart to use the named range I currently have.

Does anyone know of a non-VBA way of making this work?

Thanks,
H

No, it's not possible.


You can use named ranges to decide dynamically which points in the existing series are displayed, subject to conditions you set.


You cannot use the same logic to change the number of series in the chart. You cannot use named ranges to add/remove series dynamically.


If you want to change the number of series, some options:

- add/delete them manually
- use a filter to hide manually some rows (or hide them manually)
- use vba


In the case of a stacked chart you could add the maximum number of series from the start and only those populated will be displayed. The legend of the chart does not, however, adjust automatically.

 

[hstaubyn]

Thanks for the reply.

I'm not sure that will work, I have tried it with the workbook name in the formula. This works for, say, an XY scatter chart, but doesn't seem to for a stacked bar chart.

As background to the problem, I have a list of jobs, each of which contribute to the total production for the year. Their contribution is broken down by month which is what appears in the chart. Each job's contribution to a given month is represented by a coloured bit of the bar.

When a new year comes along, the values in the cells which the chart reads from automatically change. Some jobs end up showing '0' for the whole year, I need to turn these off on the chart otherwise a pile of useless jobs builds up in the legend. For this reason, my dynamic range covers only those jobs which contribute in the current year.

I have resorted to some code now which does the job nicely, updating the chart every time you open the worksheet:

Sub UpdGraphSer()

Dim ChtSer As Series

ActiveSheet.ChartObjects("Monthly Totals").Activate

For Each ChtSer In ActiveChart.SeriesCollection
    If ChtSer.Name <> "Target" And ChtSer.Name <> "Low Target" Then
        If Application.WorksheetFunction.VLookup(ChtSer.Name, Range("AllJobIndex"), 14, False) = 0 Then
            ChtSer.Delete
        End If
    End If
Next ChtSer

End Sub

(The 14th column in that reference table is equal to 1 if the job contributes in the current year)

 

[hstaubyn]

Thanks PGC, that did feel like the inevitable conclusion. If anyone else has a similar issue, please see above for a VBA work around.

 

[BiocideJ]

I see where I was thinking about your problem wrong. I was thinking your COLUMNS were your series when in fact it was your ROWS. PGC is indeed correct, however, I am glad to see you found a VBA work-around.