Dynamic Chart Update Issue
 

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?

Dynamic Chart Update Issue
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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?

Dynamic Chart Update Issue
 

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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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?

Dynamic Chart Update Issue
 

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("MyDynamicRa ngeName")

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



"Themd" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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?

Dynamic Chart Update Issue
 

Thank you very much for your help.

"Jon Peltier" wrote:

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("MyDynamicRa ngeName")

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



"Themd" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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?

Dynamic Chart Update Issue
 

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

Dynamic Chart Update Issue
 

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.

"Themd" wrote:

Thank you very much for your help.

"Jon Peltier" wrote:

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("MyDynamicRa ngeName")

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



"Themd" wrote in message
...
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" wrote:

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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?

Dynamic Chart Update Issue
 

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/ChartsH...ameAChart.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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Themd" wrote in message
...
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