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
[Solved] Err:520 in a cell
[Solved] Err:520 in a cell
Last edited by robleyd on Tue Aug 03, 2021 1:19 pm, edited 1 time in total.
Reason: Tag [Solved]
Reason: Tag [Solved]
OpenOffice 4 on Microsoft Windows 10 Enterprise
Re: Err:520 in a cell
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
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.
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.
- MrProgrammer
- Moderator
- Posts: 4895
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Err:520 in a cell
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 trying to opean a xlsx file in OpenOffice 4.
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:I'm not familiar with OpenOffice formulas …
Maybe, maybe not. You do not show us the formula in OpenOffice so we cannot know.jordias wrote:The formula on OpenOffice looks like it's been cutted (not complete).
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:jordias wrote: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;"")&")"
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.jordias wrote:… it returns a Err:512
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).
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).
Re: Err:520 in a cell
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
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