Hi Thomas,

Usually, the workaround is to use indirect addressing - the worksheet
functions INDIRECT(ADRESS...)) as formulas in a worksheet range. Then
chart the worksheet range - which does not change.

If you want to use VBA to change chart ranges, I find it best to set
a range variable and use a statement like,
Chart.SeriesCollection(i).Values = myRange

There is an example at http://www.edferrero.com/charting.aspx
look at 'Chart Selector'

Ed Ferrero
http://www.edferrero.com

Hi,

I have an issue I doubt there is a solution for so hold on now.
I have a sheet with a number of dynamically defined data series that I
plot in a chart. Now I want to be able to multiply this sheet to use up
to 20 similar sheets in the same workbook. The thing is that I want the
references in the charts on each sheet to refer to corresponding sheet
and not to the original one. I have found a way to use the indirect
fomula combined with a VBA script to automatically define named ranges
for the series on each page. The only thing missing is to make the
charts plot these named ranges based on a similar approach using the
indirect formula (and thereby refering to the correct range names
defined in celles in each sheet). The chart series field does however
not accept the indirect formula as input.

Would really appreciate if anyone had a solution or workaround for
this!

Regards//Thomas