On my web site and blog, I have over a dozen pages that talk about dynamic charts. A partial list of these articles is provided at the end of this post.
I was asked to expand on one of my examples, specifically to show a dynamic chart with multiple dynamic series.
I made up the following data set:
I created the following chart, and when the first series was selected, the SERIES formula showed the cell addresses of the ranges defining its data.
I defined the following dynamic names:
The OFFSET formula defining XLabels basically says, starting with cell A1, move down one cell and right zero cells, and give us the range which is as tall as the number of labels, given by the COUNTA function, and as wide as 1 cell. The other OFFSET formulas merely say, give us the range 1, 2, 3, or 4 columns to the right of XLabels. This is a shortcut that assures all dynamic ranges are the same size.
To link the series to the dynamic ranges, all that is required is to replace the static cell references in the SERIES formulas with the names defined above. Here is the first SERIES formula when the cell addresses have been replaced by the names, before pressing Enter.
Since the names were defined with the workbook as their scope, pressing Enter converted the references to workbook references, that is, referenced to MultipleDynamicChart.xls instead of Sheet1.
When data is added to the range…
… the SERIES formula is not changed, but the ranges defined by the dynamic names does change, so the chart updates.
The workbook with this example can be downloaded as a zip file, MultipleDynamicChart.zip.
Here is a partial list of my articles on dynamic charts.
- On the PTS Blog
- Dynamic Charts
- Dynamic Chart using Pivot Table and Range Names
- Dynamic Chart using Pivot Table and VBA
- Dynamic Ranges to Find and Plot Desired Columns
- Split Data Range into Multiple Chart Series without VBA
- VBA to Split Data Range into Multiple Chart Series
- Dynamic Chart Source Data (VBA)
- Display One Chart Dynamically and Interactively
- On the Peltier Tech web site