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

customize chart legend

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


Joined: 07 Jul 2006
Posts: 18

PostPosted: Fri Apr 20, 2007 6:14 am    Post subject: customize chart legend Reply with quote

We have a lot of Excel97 spreadsheets that we want to convert to OpenOffice.

Chart legends that are defined in Excel as (for example) "red cars, blue cars..." get default names like "row AA, row BB..."
One possible solution is to add an extra column (or row) before (or above) the actual data range and select the "First row as label" option (or "First column as label")
However, these rows/columns are already filled with other data, so to do this we have to rebuild a lot of the spreadsheet.
In Excel you can define "series" to be used as labels. Can a similar thing be done in Calc?

Thanks.

GJ
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Apr 20, 2007 9:07 am    Post subject: Reply with quote

I don't think you have to rebuild the spreadsheet when you insert cells or an entire column. The spreadsheet notices insertion and removal of cells and adjusts all references within this document.
Create a copy of your document and try:
Select the column of cells where you want to insert new cells.
Call Insert>Cells...
Choose option "Move to right" and confirm.
Now you have blank new cells left of the previously selected range and all references to the moved cells right of the new cells should be adjusted properly.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
gjwerler
General User
General User


Joined: 07 Jul 2006
Posts: 18

PostPosted: Sun Apr 22, 2007 10:55 pm    Post subject: Reply with quote

Thank you for your help.

Yes, you're right. If someone who knows how to build a spreadsheet has made the spreadsheet. However, in the migration process of MS Office to OO we have discovered a lot of spreadsheets that included formulas that pointed to hard coded row and column numbers. Also, a lot of VBA code is using hard coded row and column numbers (we are using the Novell OO witch can execute most of our VBA code).

So, in those cases you don't want to mess with the spreadsheet itself. If OO does not have a possibility to edit the chart legend we *have* to do it the way you suggested. I just wanted to make sure that there isn't an other alternative (like the one offered in MS Office).
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 Apr 23, 2007 11:21 am    Post subject: Reply with quote

Ah, I see. Lots of INDEX, OFFSET, INDIRECT and alike.
I just found out that you can use separate labels indeed. This is what I tried:
$Sheet1.$A$1:$C$16 has arbitrary numbers and I created a default chart from this range.
Then I put some labels in another sheet $Sheet2.$A$1:$A$16, changed the chart's datarange to a semicolon-separated list of columns $Sheet2.$A$1:$A$16;$Sheet1.$A$1:$C$16 with option "First column has headers". This did not change anything until I moved Sheet1 behind Sheet two. Now the range shows x-axis labels of $Sheet2.$A$1:$A$16. The order of sheets seems to be significant for option "First column has labels".
Hope this helps.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
gjwerler
General User
General User


Joined: 07 Jul 2006
Posts: 18

PostPosted: Tue Apr 24, 2007 12:04 am    Post subject: Reply with quote

Nice workaround!

When I try to reproduce your example using a "Lines" chart, the X-axis now shows the labels from sheet2.
The legend still shows Column A, Column B and Column C.

Can the legend be changed as wel using this method? I tried several examples but could't find a valid range that let me define the first row and column in a separate sheet.

Thanks for you help.
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