Excel 2007 : find & replace for graph series

[roland willems]

can I use 'Find and Replace' function to change the data-references for series in graphs ?

[pike]

Hi roland willems
No, don't think you can. Your post should accurately and concisely describe your problem, not your anticipated solution.

[roland willems]

This is the situation;
I have multiple sheets with data and 1 general sheet with all graphs.
All graphs are identical copies refering to individual datasheets.
To change all references to individual sheets, I have to open the graphs design tab to manually change the X-and Y series values for this graph.
Can this be done by 'find and replace' function rather than do it manually ?

[pike]

You can loop through the charts and change the values in the data

Please Login or Register  to view this content.

or use indirect values and name ranges but not find replace.

[teylyn]

Roland,

There are ways to set up such scenarios where you do not have to change the chart series definitions. This is one of the techniques of dynamic charts.

I assume that you have charts where you want to display the same kind of data, but for different parameters, like time frames or other categories.

The more efficient way is to build one table for the graph, then populate that table with lookup formulae that will pull the data that you are interested in.

For example: you have data sets for every month. Column A is January, B is Feburary, C is March. In the chart, you would normally reference column A if you want to build a chart for January. But then, you need to change the chart series definition if you want to show the chart for March.

So, create a column that is dedicated to the chart, say Column Z. In this column you have formulas that pull the values for the month you want to chart. In a field on the sheet, for example X1 you specify the month, then column Z will populate with the data for that month. Build the chart on the data of column Z, then you only have to change X1 to a different month and the chart will display that data.

See a very simple example attached.

[eriol]

I f I didn't get it wrong what you want to do this might help, from Jon Peltiers blog:

http://peltiertech.com/WordPress/how...ries-formulas/

there is also add-in to serach and replace chart series.

regards, eriol

[roland willems]

Thanx Jon
nice addin to use

[shepl123]

Hi Teylyn,
The example you posted is exactly what I need, but I cannot seem to be able to figure out how to implement your design. Will you give step by step instructions, or point me to primary source? I know that this is an old thread, but I'm looking for whatever help I can get... Thanks!