Conditional Formatting messed up (back from LOcalc to Excel)


I had an issue with compatibility of LO Calc with MS Excel… Excel was refusing to enter a simple formula in a complex file and crashing.
So i opened in LO Cacl 6.5 and i could finally enter that formula, and save normally.
Opening back in MS Excel 2016 pro, it gave an error message saying trying to recover information, and the window showed lines about conditional formatting. Once the file opened in MS Excel: conditional formatting that was entered through rules containing formulas was all gone!!

I had to spend a hell of time, lost several hours fixing it all gain…
What exactly happened?



The Excel Sheet had in the first column the formulas


Than numerous other column with complex formulas. Than i wanted to change it to:


CellA2=rows($A$1:A2)-1 (I had calculations dependent on the value in this cell being a numbering of items).

The file contained several sheets all filled with INDEX/MATCH, SUMPRODUCT/OFFSET functions so at some point Excel did not allow me to even do the simple change for Column A and it was crashing. I reverted to LO Calc. It did allow me to do the change for Column A cell A2 and downwards.
I opened back in Excel, had an error message. Opened the file after the suggested recovery, and all the conditional
formatting was not appearing.
The file initially contained conditional formatting with several rules each rule with a formulas like this:
For the entire selection:

=AND($C1="2-Raw Materials",MOD(ROWS($C$1:$C1),2)=0)

Than the cell color is light orange with dark orange borders.


Than the cell color is light green with dark green borders.

It was working fine in Excel before the change for Column A in LO Calc.

The total number of rules was 8.

Opening in Excel after the fix in LO Calc:
I could not see anything colored!
I checked from the conditional formatting tab:
The formulas were still there but the coloring was replaced with no format for all rules.

LO Calc: 6.3.5
MS Excel 2016 PRO.

What exactly went wrong?

You used a foreign file format. What did you expect? Work in ODF only.

You don’t tell what exactly were you trying to enter (that you think “simple”, but Excel disliked). If it didn’t want it there, then why you expect it to accept it when it’s forced like you did?

It would be interesting indeed to understand the problem - but that requires a test file, and description what should be put where to get the problem. A bug report is welcome.

Post the Validation, probably used something that the Calc has and Excel does not

@Fouad_Hoblos, Are you saving on ods?

ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.