OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

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

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
fritzm
Newbie
Newbie


Joined: 19 Nov 2010
Posts: 4

PostPosted: Mon Nov 22, 2010 2:02 pm    Post subject: Charts : non-absolute sheet reference for data range broken? Reply with quote

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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Mon Nov 22, 2010 4:19 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Nov 22, 2010 4:19 pm    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
fritzm
Newbie
Newbie


Joined: 19 Nov 2010
Posts: 4

PostPosted: Tue Nov 23, 2010 1:03 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
JNG
Newbie
Newbie


Joined: 10 Sep 2012
Posts: 1

PostPosted: Mon Sep 10, 2012 12:01 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group