[Solved] Err:520 in a cell

Discuss the spreadsheet application
Post Reply
jordias
Posts: 2
Joined: Mon Aug 02, 2021 10:50 pm

[Solved] Err:520 in a cell

Post by jordias »

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
Last edited by robleyd on Tue Aug 03, 2021 1:19 pm, edited 1 time in total.
Reason: Tag [Solved]
OpenOffice 4 on Microsoft Windows 10 Enterprise
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Err:520 in a cell

Post by RusselB »

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
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Err:520 in a cell

Post by MrProgrammer »

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
jordias
Posts: 2
Joined: Mon Aug 02, 2021 10:50 pm

Re: Err:520 in a cell

Post by jordias »

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
OpenOffice 4 on Microsoft Windows 10 Enterprise
Post Reply