determining and existing charts data source

[ztRon]

Is it possible to determine the range a chart is using for its datasource?

I am updating a series of list objects in a xls with a varying number of
rows and want to determine if any charts are refering to my list objects and
if they are, set the datasources to the new range.

 

[Jon Peltier]

You can look at the series formulas. If you're doing this in VBA, you could
use John Walkenbach's class module to parse the series formulas:

http://www.j-walk.com/ss/excel/tips/tip83.htm

If you're using Excel 2003, don't formulas (including series formulas) that
refer to columns in the list update when the list expands or shrinks? If
it's 2007, the same dynamic behavior is related to tables.

If it's neither of these versions, make a chart based on dynamic names,
which update as the range changes size. No need for complex VBA. Dynamic
Charts:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html

- Jon