[Solved] Error 508 opening Excel file

Discuss the spreadsheet application
Post Reply
ejmasjr
Posts: 3
Joined: Sun May 12, 2019 1:38 am

[Solved] Error 508 opening Excel file

Post by ejmasjr »

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
 Edit: Changed subject, was Error 506 
Make your post understandable by others 
-- MrProgrammer, forum moderator 
Last edited by MrProgrammer on Wed May 11, 2022 3:30 am, edited 2 times in total.
Reason: Subject for topic was wrong; error code was 508 not 506
OpenOffice 4.1.6 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error 506

Post by FJCC »

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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
ejmasjr
Posts: 3
Joined: Sun May 12, 2019 1:38 am

Re: Error 506

Post by ejmasjr »

Vacation Register.xlsx
(14.35 KiB) Downloaded 129 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
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
ejmasjr
Posts: 3
Joined: Sun May 12, 2019 1:38 am

Re: Error 506

Post by ejmasjr »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Error 506

Post by FJCC »

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

=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

=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

=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.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Error 506

Post by Zizi64 »

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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Error 506

Post by RoryOF »

There seem to be at least two unmatched closing brackets in the formula.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
MrProgrammer
Moderator
Posts: 4895
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Error 506

Post by MrProgrammer »

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 121 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.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Post Reply