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

Condition Formatting compatibility with Microsoft Office2007

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


Joined: 12 Oct 2011
Posts: 9
Location: United Kingdom

PostPosted: Wed Oct 12, 2011 7:41 am    Post subject: Condition Formatting compatibility with Microsoft Office2007 Reply with quote

I created a spreadsheet in Office with a lot of conditional formatting in it. Basically it was just a column that, when the word "Yes" was written in it would turn the entire row green.

That's all there was but this was for every line, of which there are hundreds and hundreds.

Anyway, I found that when I open it in Libre Office and make an edit, and save it, when I re-open it in Office all the conditional formatting has stopped working.

I can't quite figure it out but I think it's something to do with the (probably messy and basic) way I do conditional formatting (copy, paste 'formats' across every line etc).

Is this a known issue or is it something more specific? Sorry if it needs more info - let me know and I'll try provide it!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Oct 12, 2011 8:14 am    Post subject: Reply with quote

Use PDF for reading and the old binary xls format for co-editing documents. OOXML has been introduced for more incompatibility (and to have something with Office and Open in the name).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Alex_Ross1983
General User
General User


Joined: 12 Oct 2011
Posts: 9
Location: United Kingdom

PostPosted: Wed Oct 12, 2011 8:32 am    Post subject: Reply with quote

Thanks for the reply. Reading a document isn't a problem, it's when I edit it then save it.

I'm 95% certain I've tried it with the old office format but I'll try it again.

When you say OOXML, what do you mean? Is that a file format in Libre Office?

Thanks again!
Back to top
View user's profile Send private message Visit poster's website
Alex_Ross1983
General User
General User


Joined: 12 Oct 2011
Posts: 9
Location: United Kingdom

PostPosted: Wed Oct 12, 2011 8:34 am    Post subject: Reply with quote

Thanks for the reply. Reading a document isn't a problem, it's when I edit it then save it.

I'm 95% certain I've tried it with the old office format but I'll try it again.

When you say OOXML, what do you mean? Is that a file format in Libre Office?

Thanks again!
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Oct 12, 2011 9:18 am    Post subject: Reply with quote

OfficeOpenXML (docx, xlsx, pptx), the file format introduced by MS Office to fight ODF.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Alex_Ross1983
General User
General User


Joined: 12 Oct 2011
Posts: 9
Location: United Kingdom

PostPosted: Thu Oct 13, 2011 4:27 am    Post subject: Reply with quote

Tried using .xls for editing but it was the same thing.

What happens is this:

The conditional format will say =$E1="Yes"

and this will work fine, when I enter 'Yes' it changes the colour of the cell as I wish.

When I edit and save this in Libre Office (did the same in Open Office too) and open it in Microsoft Office, all the formatting has stopped working and I get this:

=Worksheetname!$E1="Yes"

Where 'worksheetname' is as you've guessed whatever the name of the specific worksheet is.

To make the formatting work again I simply delete the worksheet name and the formula works, what I can't understand is why saving it in LO (which works fine when I do save it) and then opening it in Office, changes the formula. Presumably it's some way Office reads the formula when it's been saved by LO?
Back to top
View user's profile Send private message Visit poster's website
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Oct 13, 2011 5:41 am    Post subject: Reply with quote

When you open the xls in Calc it should read
=Worksheetname.$E1="Yes"
with point as sheet separator.
Working with Calc you use the point as sheet separator and xls import will automatically show a point instead of the exclamation mark.
The list separator (comma or semicolon) in LibreOffice depends on Tools>Options>LanguageSettings>Languages>LocaleSetting just like Excel uses the semicolon in most localized versions.
In OOo the list separator is always semicolon.
See help chapter on operators.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
Alex_Ross1983
General User
General User


Joined: 12 Oct 2011
Posts: 9
Location: United Kingdom

PostPosted: Sat Oct 15, 2011 2:46 pm    Post subject: Reply with quote

Thanks, I checked and as you say the Conditional Format is worksheetDOT, but obviously Excel is converting this for some reason, which is what is causing the problem.

If I save the XLS file and then open it in Excel something along the way turns it into what I described above...

UPDATE: I checked and the precise line of the conditional formatting when I open it in Libre Office is this:

$Achievements.$E1278="Yes"

Achievements is the worksheet name.

After I edit and save the document the format is still the same.

So it's something about Excel interpreting the file after it has been edited and saved by LO that is converting it. Any idea what?
Back to top
View user's profile Send private message Visit poster's website
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