Dynamic Chart Update Issue

[Themd]

Dear all,

I have a data tape with is filtered based on a criteria and is copied to
another separate sheet. On this staging sheet I use offset and counta
functions to dynamically set a range which I use for a chart. Every time the
data changes the range changes as I want.

On the chart file I insert the name of the range to the data source field
ie. Sheet1!Rangename then it displays the data correctly. however if the
range expands to 5 rows from the initial 4 it still displays the initial 4.
I once read that you needed to create a vba code to make sure that ever time
it is run it resets the named range. Can you help?

 

[Jon Peltier]

If the range name is truly dynamic, you don't need VBA. Make sure
calculation is set to automatic.

What version of Excel are you using? I have heard that sometimes 2007 does
not update as expected.

- Jon

 

[Themd]

Jon,

I have actually used one of your dynamic examples. I am using office 2003.
What happens is that the data updates but if the range was only 5 row long it
is stuck at 5 rows. It only updates if reset the data source of the chart.
In your examples, you use dynamic series. I have made a dynamic range so
that the chart will chart 5-6 or 7 series at once.

it is something like this:

1 2 3 4 5 6 7 8 9
A
B
C
D
(and sometimes)
E
F

So my name range covers it all and expands based on counta. I insert the
named range in to the row data source of the chart and not in to the series
formula like you do.

I hope I did not confuse you?

Appreciate all help.

 

[Jon Peltier]

It doesn't work this way. You can only use names as the separate ranges for
the name, X values, and Y values of an individual series. When you use a
name in the Data Range box, Excel accepts it, but remembers the cell address
of the range.

You could do this with a filter, to hide rows without data (when E and F are
empty), because by default Excel won't plot hidden rows. Or you could use
VBA to reassign the source data of the chart to the named range:

ActiveChart.SetSourceData
Source:=Worksheets("DataSheet").Range("MyDynamicRangeName")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

 

[Themd]

Thank you very much for your help.

It doesn't work this way. You can only use names as the separate ranges for
the name, X values, and Y values of an individual series. When you use a
name in the Data Range box, Excel accepts it, but remembers the cell address
of the range.

You could do this with a filter, to hide rows without data (when E and F are
empty), because by default Excel won't plot hidden rows. Or you could use
VBA to reassign the source data of the chart to the named range:

ActiveChart.SetSourceData
Source:=Worksheets("DataSheet").Range("MyDynamicRangeName")

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______

 

[Themd]

Thanks again for your help,

One last question. If you have more than one chart on a page, how do you
alter the formula you have provided. I have 3 charts and they each need the
amendment you have suggested. Also I assume you insert this in to the sheets
coding and not as a macro right?

Appreciate your patience.
Aksel

 

[Themd]

Thanks again for your help,

One last question. If you have more than one chart on a page, how do you
alter the formula you have provided. I have 3 charts and they each need the
amendment you have suggested. Also I assume you insert this in to the sheets
coding and not as a macro right?

Appreciate your patience.

 

[Jon Peltier]

If there are three charts, you need to differentiate them. You need three
chart names (see the 'embedded charts' section of this article:
http://peltiertech.com/Excel/ChartsHowTo/NameAChart.html) and three range
names.

If you are clever, you can give the charts and ranges sequential names, like
TheChart1, TheChart2, TheChart3 and TheRange1, TheRange2, TheRange3.
The code becomes:

For iChart = 1 to 3
ActiveSheet.ChartObjects("TheChart" & iChart).Chart.SetSourceData _
Source:=ActiveSheet.Range("TheRange" & iChart)
Next

The code can be used in a Worksheet_Change event procedure in the code
behind the worksheet, in which case it would update dynamically. The code
would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iChart As Long
For iChart = 1 to 3
Me.ChartObjects("TheChart" & iChart).Chart.SetSourceData _
Source:=Me.Range("TheRange" & iChart)
Next
End Sub

Me is used to refer to the worksheet that contains this code in the
corresponding module.

- Jon