Apache OpenOffice Community Forum

Hi,
I'm trying to opean a xlsx file in OpenOffice 4.
The file opens but one of the cells contains a very big formula which returns a Err:520.
The formula on OpenOffice looks like it's been cutted (not complete).
I copy paste the complete formula inside the OpenOffice file cell and it returns a Err:512.
I'm not familiar with OpenOffice formulas so, I'm a little bit stuck. This is the formula in xlsx:

=IF(FPL!BX6<>0;"FF "&FPL!BX6&CHAR(10);"")&IF(FPL!BX7<>0;FPL!BX7&CHAR(10);"")&IF(FPL!BX8<>0;FPL!BX8&CHAR(10);"")&IF(FPL!AG13<>0;"AD ";"")&IF(FPL!AG13<>0;FPL!AG13&CHAR(10);"")&IF(FPL!Z14<>0;FPL!Z14&CHAR(10);"")&"(FPL-"&FPL!CD16&"-"&FPL!FQ16&FPL!HJ16&CHAR(10)&"-"&FPL!AJ18&FPL!CD18&"/"&FPL!EQ18&"-"&FPL!FU18&"/"&FPL!HI18&CHAR(10)&"-"&FPL!BE20&FPL!DQ20&CHAR(10)&"-"&FPL!AI22&FPL!CA22&" "&IF(FPL!DU22=0;CHAR(10);FPL!DU22&CHAR(10))&IF(FPL!Z25<>0;FPL!Z25&CHAR(10);"")&IF(FPL!Z26<>0;FPL!Z26&CHAR(10);"")&IF(FPL!Z27<>0;FPL!Z27&CHAR(10);"")&IF(FPL!Z28<>0;FPL!Z28&CHAR(10);"")&IF(FPL!Z29<>0;FPL!Z29&CHAR(10);"")&IF(FPL!Z30<>0;FPL!Z30&CHAR(10);"")&"-"&FPL!AY34&FPL!CT34&" "&FPL!ES34&" "&FPL!GS34&CHAR(10)&IF(FPL!AI37=0;"-";"-"&FPL!AI37&CHAR(10))&IF(FPL!Z38<>0;FPL!Z38&CHAR(10);"")&IF(FPL!Z39<>0;FPL!Z39&CHAR(10);"")&IF(FPL!Z40<>0;FPL!Z40&CHAR(10);"")&IF(FPL!Z41<>0;FPL!Z41&CHAR(10);"")&IF(FPL!Z42<>0;FPL!Z42&CHAR(10);"")&IF(FPL!AV46<>0;"-E/"&FPL!AV46&" ";"")&IF(FPL!DC46<>0;"P/"&FPL!DC46&" ";"")&IF(OR(FPL!GC46=0;FPL!GT46=0;FPL!HK46=0);"R/"&C1&D1&E1&" ";"")&IF(OR(FPL!BO50=0;FPL!CF50=0;FPL!CX50=0;FPL!DP50=0);"S/"&H1&I1&J1&K1&" ";"")&IF(OR(FPL!FL50=0;FPL!GC50=0;FPL!GT50=0;FPL!HK50=0);"J/"&N1&O1&P1&Q1&" ";"")&IF(FPL!AV54<>0;"D/"&FPL!AV54&" "&FPL!BS54&" "&"C "&FPL!DL54;"")&CHAR(10)&IF(FPL!AV57<>0;"A/"&FPL!AV57&" ";"")&IF(FPL!AV59<>0;"N/"&FPL!AV59;"")&IF(FPL!AV62<>0;CHAR(10)&"C/"&FPL!AV62;"")&")"

Some help would be appreciated.
Regards
Jorge Dias
Portugal

Please post the Calc formula. In order to have the formula show correctly, enclose the formula in code tags. They are in the format of [ code ] and [ /code ] (though without the space between the square bracket and the word).
Better yet attach a sample of your file. See this tutorial for assistance

jordias wrote:I'm trying to opean a xlsx file in OpenOffice 4.

OpenOffice CANNOT create XLSX files, so I know the file wasn't created in OpenOffice. You should use the same program to open the file that was used to create the file. You should not expect to get good results if you use a different program. If the file was created in Excel, use Excel. If the file was created in LibreOffice, use LibreOffice.

jordias wrote:I'm not familiar with OpenOffice formulas …

Then it is particularly important for you to use the same program to open the file as was used to create it, since you cannot resolve any problems introduced by attempting to use a different one.

jordias wrote:The formula on OpenOffice looks like it's been cutted (not complete).

Maybe, maybe not. You do not show us the formula in OpenOffice so we cannot know.

jordias wrote:This is the formula in xlsx:

What a mess!! For this situation, the main difference will be the sheet separator. Excel uses the bang (!) while Calc uses the period (.). Thus a translation to Calc would be:

=IF(FPL.BX6<>0;"FF "&FPL.BX6&CHAR(10);"")
&IF(FPL.BX7<>0;FPL.BX7&CHAR(10);"")
&IF(FPL.BX8<>0;FPL.BX8&CHAR(10);"")
&IF(FPL.AG13<>0;"AD ";"")
&IF(FPL.AG13<>0;FPL.AG13&CHAR(10);"")
&IF(FPL.Z14<>0;FPL.Z14&CHAR(10);"")
   &"(FPL-"
   &FPL.CD16
   &"-"&FPL.FQ16
   &FPL.HJ16
   &CHAR(10)
   &"-"&FPL.AJ18
   &FPL.CD18&"/"
   &FPL.EQ18&"-"&FPL.FU18
   &"/"
   &FPL.HI18
   &CHAR(10)
   &"-"
   &FPL.BE20
   &FPL.DQ20
   &CHAR(10)
   &"-"
   &FPL.AI22
   &FPL.CA22
   &" "
&IF(FPL.DU22=0;CHAR(10);FPL.DU22&CHAR(10))
&IF(FPL.Z25<>0;FPL.Z25&CHAR(10);"")
&IF(FPL.Z26<>0;FPL.Z26&CHAR(10);"")
&IF(FPL.Z27<>0;FPL.Z27&CHAR(10);"")
&IF(FPL.Z28<>0;FPL.Z28&CHAR(10);"")
&IF(FPL.Z29<>0;FPL.Z29&CHAR(10);"")
&IF(FPL.Z30<>0;FPL.Z30&CHAR(10);"")
   &"-"
   &FPL.AY34
   &FPL.CT34
   &" "
   &FPL.ES34
   &" "
   &FPL.GS34 
   &CHAR(10)
&IF(FPL.AI37=0;"-";"-"&FPL.AI37&CHAR(10))
&IF(FPL.Z38<>0;FPL.Z38&CHAR(10);"")
&IF(FPL.Z39<>0;FPL.Z39&CHAR(10);"")
&IF(FPL.Z40<>0;FPL.Z40&CHAR(10);"")
&IF(FPL.Z41<>0;FPL.Z41&CHAR(10);"")
&IF(FPL.Z42<>0;FPL.Z42&CHAR(10);"")
&IF(FPL.AV46<>0;"-E/"&FPL.AV46&" ";"")
&IF(FPL.DC46<>0;"P/"&FPL.DC46&" ";"")
&IF(OR(FPL.GC46=0;FPL.GT46=0;FPL.HK46=0);"R/"&C1&D1&E1&" ";"")
&IF(OR(FPL.BO50=0;FPL.CF50=0;FPL.CX50=0;FPL.DP50=0);"S/"&H1&I1&J1&K1&" ";"")
&IF(OR(FPL.FL50=0;FPL.GC50=0;FPL.GT50=0;FPL.HK50=0);"J/"&N1&O1&P1&Q1&" ";"")
&IF(FPL.AV54<>0;"D/"&FPL.AV54&" "&FPL.BS54&" "&"C "&FPL.DL54;"")&CHAR(10)
&IF(FPL.AV57<>0;"A/"&FPL.AV57&" ";"")
&IF(FPL.AV59<>0;"N/"&FPL.AV59;"")
&IF(FPL.AV62<>0;CHAR(10)&"C/"&FPL.AV62;"")&")"

jordias wrote:… it returns a Err:512

That means the monster formula is too complex for Calc and you should use the program which created the file to open it. If you understood Calc formulas you could, with some work, simplify it. For example, instead of 55 lines of contatenation you could put =IF(FPL.BX6<>0;"FF "&FPL.BX6&CHAR(10);"") in cell α, =IF(FPL.BX7<>0;FPL.BX7&CHAR(10);"") in cell β, =IF(FPL.BX8<>0;FPL.BX8&CHAR(10);"") in cell γ, and so on, then concatenate α, β, γ, … instead of the monster formula. But this will be difficult until you learn how to use Calc. If you're interested in learning that, go to your local library and borrow a book on spreadsheets. Any book from the last 10 years will be fine, even one written for Excel.

Dear all,
I appreciate all your help (some have given you a lot of work, I appreciate that).
I just discovered (as one of you said) that I can split it in 2 cells and concatenate those and the problem is solved.
Kind regards
Jorge Dias