Charts : non-absolute sheet reference for data range broken?

[fritzm]

I want to have a spreadsheet with a number of sheets; the layout of each sheet is identical and consists of two rows of data at the top, followed by six bar graphs which display that data graphically. The only difference between the sheets is that the data values change for each one.

I started by setting up the first sheet, got all the graphs formatted as I wanted them, then copied that sheet to a second one. Renamed the copy, and updated the data in the top rows. Then realised that the graphs had not changed when I changed the data because the graph data ranges were specified with an absolute sheet reference, so they still pointed to the first sheet.

So I deleted my second sheet, went back to the first and changed the sheet references to non-absolute in all the graph data ranges (deleted the $ in the range specifications), and then copied the sheet. To my surprise, I found that in the copy, all the sheet references were now absolute references to the first sheet. In addition, I realised that changing the sheet references to relative in the first sheet had messed up the charts - some of the bars were missing.

So I undid all of what I had done, deleted my second sheet and made a fresh copy of the first sheet - absolute sheet references and all. I had the thought that I could then use find and replace to change the sheet reference for all the data ranges in the charts, but find and replace doesn't find anything if I select the range containing the charts. Stumped again.

So I'm asking if there is any way to copy sheets containing charts and have the data ranges refer to the copy each time, instead of to the original sheet; alternatively, is there an easy way to update the data ranges in a bunch of charts ?

Thanks,

Fritz

[ken johnson]

Instead of copying the sheet to the same doc I sent the copy to a new doc then sent the copied sheet in the new doc back to the original doc.
When done this way the chart on the copied sheet refers to cells on the sheet that it is on, not the cells on the original sheet that was copied.

Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).

[Villeroy]

Have a look at this: http://user.services.openoffice.org/en/forum/download/file.php?id=3004
Scenarios are hidden data sheets and you can load their content into the same formatted sheet with charts and everything without the need to duplicate anything.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[fritzm]

Scenarios do exactly what I want - thanks for the tip. What do the options Copy back, Copy entire sheet, and Prevent Changes in the dialog do ? Help doesn't explain them.

I do, however, still feel that relative sheet references in charts are not behaving properly when the sheet is copied - they should remain relative, not revert spontaneously to absolute.

Fritz

[JNG]

I have had the same problem when copying from sheet1 ie I find the data references are set to sheet1. If I change the Sheet number to that of the new Sheet in the Data Range then it automatically changes the Sheet number in all of the Data Series.