Can you make a chart's data *always* refer to a dynamic range name?

[Chrisso]

Hi All

I have a dynamic range name that includes all my chart data including
rows and columns as the number of both change a daily basis. So one
day I will have three row series and four columns and another six
series and two columns of data.

I can tell my chart to show all the data by setting the data range to
my dynamic name and that works fine but it converts the dynamic name
to row and column references and forgets the dynamic part for the next
day. I then have to manually perform the same set the next day so that
it charts all the series and data again.

I would like to automate this in VB but this is complicated as all my
charts live on a separate sheet and it is painful in VB to work out
which chart should have which dynamic range name.

How can I get my charts to remember the dynamic range name and not
convert to rows and columns?

If this is not possible what is the best practice to loop over all my
charts and reset their data to the appropriate range name?

At the moment my approach is:
* name each chart the same as the dynamic range name
* loop through the charts on my chart sheet, read its name then set
its data to the same dynamic range name

This is quite involved and not very robust or intuitive. What is the
best practice?

Thanks for any ideas?
Chrisso

 

[Jon Peltier]

A series will remember the defined names for its Name, X Values, and Y
Values. The entire source data range will not. If you're using a VBA
procedure, you could use a lookup table, which listed each chart along with
its parent worksheet and the source data range name. Loop down the table,
find the chart, and apply the data range using SetSourceData.

- Jon

 

[Chrisso]

A series will remember the defined names for its Name, X Values, and Y
Values. The entire source data range will not. If you're using a VBA
procedure, you could use a lookup table, which listed each chart along with
its parent worksheet and the source data range name. Loop down the table,
find the chart, and apply the data range using SetSourceData.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______














- Show quoted text -

Thanks Jon

I have implemenbted with a lookup table and this is working fine and
is easy to maintain.

Cheers
Chrisso