[Solved] Error 506

Discuss the spreadsheet application

[Solved] Error 506

Postby ejmasjr » Sun May 12, 2019 5:02 am

When I open a spreadsheet created in Excel that has a fairly lengthy formula, the column that the result is to be is 'error 506' all the way down the column.. It is a check book register that I have been using for years in Excel and would now like to access through Open Office. Any ideas? Running 4.1.6 in Windows 10.

Thanks for the help in advance
Last edited by ejmasjr on Tue May 14, 2019 12:12 am, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
ejmasjr
 
Posts: 3
Joined: Sun May 12, 2019 1:38 am

Re: Error 506

Postby FJCC » Sun May 12, 2019 6:40 am

Are you sure the error is 506? I do not see that listed in the help section on error codes.
Can you upload a small example file? One line showing the error should be enough. To upload a document, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7226
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error 506

Postby Zizi64 » Sun May 12, 2019 8:22 am

https://wiki.openoffice.org/wiki/Calc_Error_Codes

506: Invalid semicolon

The AOO and LO uses the semicolon as parameter separator character in the functions instead of the comma.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8143
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Postby ejmasjr » Sun May 12, 2019 2:45 pm

Vacation Register.xlsx
(14.35 KiB) Downloaded 35 times
Sorry I misstated the error code, was doing from memory. The correct error is 508.
I appreciate the help. I am attaching spread sheet in question as I did not generate it and don't fully understand the formula so some outside help is needed.
Thanks again....
OpenOffice 4.1.6 on Windows 10
ejmasjr
 
Posts: 3
Joined: Sun May 12, 2019 1:38 am

Re: Error 506

Postby Zizi64 » Sun May 12, 2019 3:04 pm

Vacation Register.xlsx
OpenOffice 4.1.6 on Windows 10


My first tip:

Always use the native, International Standard ODF file formats. As I know it, the AOO 4.1.6 can not save into the OOXML formats.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8143
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Postby ejmasjr » Sun May 12, 2019 3:11 pm

I appreciate your help very much, but unfortunately do not understand your answer. I don't know what standard ODF or OOXML formats are. So I have no idea what I need to do to correct this issue.
OpenOffice 4.1.6 on Windows 10
ejmasjr
 
Posts: 3
Joined: Sun May 12, 2019 1:38 am

Re: Error 506

Postby FJCC » Sun May 12, 2019 3:19 pm

I am not familiar with the style of formula used in the document. I suppose it is "intuitive" to some people, but it looks like a verbose mess to me. I believe the equivalent formula for cell H5 would be the following in OpenOffice.
Code: Select all   Expand viewCollapse view
=IF(COUNT(F5:G5) = 0;"";-SUM($F$5:F5) + SUM($G$5:G5))

You can copy/paste that down the column to fill the rest of column H. A slightly clearer version
Code: Select all   Expand viewCollapse view
=IF(COUNT(F5:G5) = 0;""; SUM($G$5:G5) - SUM($F$5:F5))

The logic of the formula is: If there are no numbers in column F and G of the current row, return an empty string of text. Otherwise, sum from G5 to the current row in column G and subtract the sum from F5 to the current row in column F.
In other words, add all the credits and subtract the sum of the debits. G5 and F5 appear twice in the SUMs because the current row is row 5 when the formula is in H5. When you copy the formula down to row six it will become
Code: Select all   Expand viewCollapse view
=IF(COUNT(F6:G6) = 0;""; SUM($G$5:G6) - SUM($F$5:F6))

LibreOffice may handle the actual Excel formula without modification but even if it does, an old timer like me would change that formula to what I wrote.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7226
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error 506

Postby Zizi64 » Sun May 12, 2019 3:34 pm

I don't know what standard ODF or OOXML formats are.


The ODF is the first industrial standard for the electronic documents, and it was was developed for the long time compatibility:
https://en.wikipedia.org/wiki/OpenDocument
ISO/IEC 26300


The OOXML standard unfortunately contains two parts. The Strict one and the Transitional one. The default file format of the MS Office is the Transitional one. But the MS often changes the rules of its own "Standard" :
https://en.wikipedia.org/wiki/Office_Open_XML
ISO/IEC 29500


Note:
There is not (never was and never will be) 100% compatibility between two different fileformats.
The native file format of the AOO and the LibreOffice are the ODF fileformats.
Use them, is you want to work efficiently with the AOO and LO.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8143
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Postby RoryOF » Sun May 12, 2019 3:40 pm

There seem to be at least two unmatched closing brackets in the formula.
Apache OpenOffice 4.1.6 on Xubuntu 18.04.3 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 29274
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Error 506

Postby MrProgrammer » Sun May 12, 2019 4:08 pm

Hi, and welcome to the forum.

ejmasjr wrote: I am attaching spread sheet in question as I did not generate it and don't fully understand the formula so some outside help is needed.
Vacation Register.xlsx wrote:=IF(COUNT(tblData[[#this row],[debit (-)]:[credit (+)]])=0,"",-SUM(INDEX([debit (-)],1):tblData[[#this row],[debit (-)]])+SUM(INDEX([credit (+)],1):tblData[[#this row],[credit (+)]]))))
We don't have that bogus Excel formula notation in OpenOffice. If you want to use formulas like the one in red, use Excel.

OpenOffice Calc uses a much simpler notation, explained in the tutorial below: =IF(COUNT(E3:F3);G2+F3-E3;"")
201905120900.ods
(15.59 KiB) Downloaded 26 times

[Tutorial] Ten concepts that every Calc user should know

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3782
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA


Return to Calc

Who is online

Users browsing this forum: No registered users and 32 guests