Condition Formatting compatibility with Microsoft Office2007

[Alex_Ross1983]

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!

[Villeroy]

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 http://forum.openoffice.org

[Alex_Ross1983]

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!

[Alex_Ross1983]

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!

[Villeroy]

OfficeOpenXML (docx, xlsx, pptx), the file format introduced by MS Office to fight ODF.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

[Alex_Ross1983]

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?

[Villeroy]

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 http://forum.openoffice.org

[Alex_Ross1983]

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?