Creating a dynamic chart for data with expanding rows and columns

[katechad]

I want to create a chart that is automatically updated, both when a new row or column is added to the spreadsheet.

I created a name for my dynamic range, using =OFFSET(Top!$B$4, 1, 0, COUNTA(B:B)-2, COUNTA(5:5)-1)]


[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Top Customers[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]Jan 12[/TD]
[TD]Feb 12[/TD]
[TD]March 12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Andy[/TD]
[TD]45[/TD]
[TD]54[/TD]
[TD]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]Beth[/TD]
[TD]35[/TD]
[TD]45[/TD]
[TD]52[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Sheba[/TD]
[TD]54[/TD]
[TD]54[/TD]
[TD]42[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]Cassandra[/TD]
[TD]82[/TD]
[TD]50[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]Sadra[/TD]
[TD]42[/TD]
[TD]22[/TD]
[TD]55[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]























I can then create a chart, using this named range as the chart data range. It worked.
However, when I add a column or a row to my data, the chart does not update as I hoped.

Most of the solutions I've looked at for dynamic charts require a name to be defined for each column in the range. This won't work for me, as I need to be able to add columns (and rows) without creating new defined names.

Can anyone help me find a solution to create a dynamic chart for data that will be adjusted both horizontally and vertically?

I am using Microsoft Excel 2010 on Windows.

 

[Derek Brown]

Removed this duplicated post - always seem to have this problem when using Google Chrome on this Board.

 

[Derek Brown]

Hello and welcome to The Board.
You will find the answer to this and many more answers and ideas about charts at Jon's excellent site:
Jon's Excel Charts and Tutorials - Index
Look for 'Dynamic Charts' in the index. You will also find that some articles have a link to download sample files.