Using find and replace with chart data

[NEWCAD]

I am trying to find a way to go into my charts and find certain source data and replace it with a different cell number, because my range changes each month it would be very helpful to be able to do all of them at once. Any suggestions would be greatly appreciated. Thanks

 

[RalphA]

Right click on the graph part of the chart, choose Source Data, select the Data Range tab. Change the range as required.

 

[NEWCAD]

Thanks Ralph, I am already doing this, I have approximately 30 graphs that need to be changed all on one sheet, I would like to be able to change them all at once rather than one at a time, either through VBA or with a find and replace type command. Thanks anyhow.

 

[Jon Peltier]

Hey Newcad -

I have a web page that describes this process, plus a macro you can download:

http://www.geocities.com/jonpeltier/Excel/Charts/ChgSrsFmla.html

Essentially it searches for one string in series formulas, replacing it by another string. For example, change column by changing "$C$" to "$D$", or row by changing "$3," to "$4," (or "$3:" to "$4:", depending where in the formula it is). Best of all, it can do one, some, or all charts on a worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

 

[DancingDavidE]

Hey Newcad -

I have a web page that describes this process, plus a macro you can download:

http://www.geocities.com/jonpeltier/Excel/Charts/ChgSrsFmla.html

Essentially it searches for one string in series formulas, replacing it by another string. For example, change column by changing "$C$" to "$D$", or row by changing "$3," to "$4," (or "$3:" to "$4:", depending where in the formula it is). Best of all, it can do one, some, or all charts on a worksheet.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Jon, does this add-in work in Excel 2007? This is exactly what I am looking for right now.

 

[Bugsy1970]

The website mentioned in this thread no longer exists - does anyone have a copy of the details/macro? or another way of achieving this would be awesome

 

[RDodds]

As of February 15, 2011 the new link can be found at http://peltiertech.com/Excel/Charts/chartvba.html.

 

[rbedenham]

I was struggling with this and came across this amazing Excel Add-in. Andrew's Excel Tips: AET Chart Tools I was trying to change hundreds of charts, manually was a nightmare, this tool did it in seconds.

 

[RDodds]

Thanks rbedenham. This looks like it fits the bill quite nicely!

 

[kcannon]

Does anyone know how to do this on a Mac?