copying charts from one Excel file to another

[Anchundan]

I've just copied a lot of data from one excel sheet, to another, in a new workbook. The charts didn't copy across, so I used the arrow in the drawing toolbar to select them all (there are 51), copied, and pasted to my new workbook.

All looks good, however, every single chart has a link back to the original workbook, which I didn't want. So now it asks me if I want to update the information every time I open this new file.

Each chart is an X-Y type, with two data series, located in exactly the same cells as the original workbook.

Is there a quick way to remove all these links back to the original workbook?

Here is an example :

Series 1 - X Values : ='E:\Work\MPI\MPI 2006-12-09\[Weight and Buoyancy7.xls]Buoyancy'!$C$35:$C$43

should be :

Series 1 - X values : ='Buoyancy'!$C$35:$C$43

I'd hate to change all this manually - it would take me hours!

 

[DAS]

You could try this:

Go to options and select the view tab. There's a checkbox by formulas. If you click in that and OK it, your spreadsheet will change to showing the formulae instead of the inputs. You could then find and replace the path with nothing. If you do replace all, it will do it in one hit. Back it up first!

 

[Anchundan]

Unfortunately, that hasn't worked. All I see now is the formulas in all the cells. But the charts remain the same.

If I want to change the source data x and y values in the charts, I still have to manually click each chart, go to Source Data, click on each series, and change the X Values nad Y Values here.

 

[Bill Door]

I have a couple of options that you could try:

If all you need to do is create a carbon copy of the original sheet in the new workbook, then open both the old spreadsheet and the new one, and then right click on the tab of the worksheet in the original and select 'move or copy', then select the new workbook as the destination and copy the sheet. This should copy all the data and the charts and keep any links intact.

If you have made significant changes to the data since the sheet was copied over then you should be able to use the following method to fix the links (with a bit of adaptation depending on what data has been altered):

With both spreadsheets open, select the full data table from the original and 'cut', then paste this data into the new spreadsheet. By cutting rather than copying the links should update to where ever the data is pasted. If you have updated the data in the new spreadsheet, you can now 'copy' that and paste over the top of the old data without destroying the links (assuming the data tables are still the same dimensions).

Hope one of these helps.

 

[Anchundan]

That works perfectly - thanks!