Dear,
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?
Complement
Okay.
The Excel Sheet had in the first column the formulas
CellA1=0
CellA2=A1+1
Than numerous other column with complex formulas. Than i wanted to change it to:
CellA1=0
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.
=AND($C1="7-Taxes",MOD(ROWS($C$1:$C1),2)=0)
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?