Global changes to links

[Jo]

Hi

Hope you can help.

I have a big spreadsheet I am working on, and wish to expand it. I have a
sheet 'Raw Data' and another sheet 'Raw Chart' where a chart is produced
charting data on the former. I have copied the 'Raw Data' sheet to a new
sheet 'Processed Data' - which I can now make a few amendments. I have then
copied the sheet containing the chart to 'Processed Chart' - but at present,
(of course) all references to data elements point at the Raw Data sheet -
whereas I want them to point to the Processed Data sheet. Is there any
simple way to globally change all the references on the chart to point to the
Processed Data sheet?

Thanks in advance!!

Jo

[John Mansfield]

Jo,

Click once on the chart in your Processed Chart sheet. Then, go to the
standard toolbar and select Chart -> Source Data. In the Source Data dialog
box -> Data Range input, change the word "Raw" to "Processed".

As a sidenote - from the macro recorder . . .

Sub ChangeSource()
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Processed
Data").Range("B2:B7"), _
PlotBy:=xlColumns
End Sub

This line refers to where you could automate the sheet reference change:

from

=Sheets("Raw Data").

to

=Sheets("Processed Data").

----
Regards,
John Mansfield
http://www.pdbook.com

"Jo" wrote:

> Hi
>
> Hope you can help.
>
> I have a big spreadsheet I am working on, and wish to expand it. I have a
> sheet 'Raw Data' and another sheet 'Raw Chart' where a chart is produced
> charting data on the former. I have copied the 'Raw Data' sheet to a new
> sheet 'Processed Data' - which I can now make a few amendments. I have then
> copied the sheet containing the chart to 'Processed Chart' - but at present,
> (of course) all references to data elements point at the Raw Data sheet -
> whereas I want them to point to the Processed Data sheet. Is there any
> simple way to globally change all the references on the chart to point to the
> Processed Data sheet?
>
> Thanks in advance!!
>
> Jo

[Jon Peltier]

Hi Jo -

I have an explanation and a small workbook with a macro to change series
formula text the same way Find/Replace does it in the worksheet.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

It's not 100% foolproof, so try it on a copy of your workbook.

You could probably just change
'Raw
to
'Processed
and it will work.

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


Jo wrote:
> Hi
>
> Hope you can help.
>
> I have a big spreadsheet I am working on, and wish to expand it. I have a
> sheet 'Raw Data' and another sheet 'Raw Chart' where a chart is produced
> charting data on the former. I have copied the 'Raw Data' sheet to a new
> sheet 'Processed Data' - which I can now make a few amendments. I have then
> copied the sheet containing the chart to 'Processed Chart' - but at present,
> (of course) all references to data elements point at the Raw Data sheet -
> whereas I want them to point to the Processed Data sheet. Is there any
> simple way to globally change all the references on the chart to point to the
> Processed Data sheet?
>
> Thanks in advance!!
>
> Jo