A Spreasheet of several Pages, constructed under Excel 2010, works fine with Ecel 2010 and 2013. Using Calc, I get on several Fields in different Pages the Error: #NAME? I tracked down to a Cell on Page 2 and found the formular: =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M") When I remove this Formular, the #NAME? - Error is gone. Some calculations on the Sites with the #NAME? - Error are not calculating any more. The Spreadsheet is from a Choral societey and on one of the not functional pages calculate summery for the finance Office. Any Idea for a Solution? Kindly yours Karl
Hi @Karl-W, thanks for reporting. Table references are not supported in your version. It will be for 5.0: https://wiki.documentfoundation.org/ReleaseNotes/5.0#Table_structured_references Please also see https://bugs.documentfoundation.org/show_bug.cgi?id=91278#c5.
Hi, I installed LibreOffice 5.0.0.5 and I sill have the same problem as already reported. =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M") still creates the Error "#Name?" I tried to Change the Name "konto" to Konto and got Err:507. When I delete "[0]!" i got Err:520. Any Help is apprecated. Karl
Please could you attach a sample file, without private data, as minimal as possible.
Created attachment 117671 [details] Error in CALC-Formula Hi, attached please find the File without private Data. I tracked down the Error to Page 2 (Auswertungen) - Cell D28. When I remove the Formula in this Cell, the Error an Page 1 (Kasse) Cell H5 disappears. However, the calculation on Page4 "Finanzamts-Auswertung" and some other pages does not work. In Excel the Formula and the whole Spread - Sheet works fine. Kind Regards Karl
Created attachment 117673 [details] Error in Calc Formula
I tried to send the original xxx.XLSX File, but it does not work. I converted the File to xxx.ODS Format and now the Cell H5 is empty, the Eroor on the other Cells are still exists. Totally confused Karl
I think it has nothing to do with Table reference the [0] guides me to the error. For the formula: =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M") The [0]! in the formula I think is the issue. How it was introduced?, It's in the xlsx uploaded file, Is this xlsx file the original that works in Excel? In the cell below Auswertungen.D29 there is a similar formula without it.
This is the original Excel-Sheet. I just delete the Membersname (Mitglieder) and the names on some other pages. The Formular ( =SUMMEWENNS(Betrag;[0]!konto;2110;VA;"M") ) is originally in Cell D28 and YES, the Spread-Sheet works perfektly in Excel. I got this spread sheet from another person that did my Job in the past. He advised me to buy the Office Package to get the thing working.
Then change to unconfirmed. Perhaps someone with excel can test.
If it is a named range with sheet scope, as I know it is not supported by LibreOffice between different sheets.
Hello, I have an similiar issue with SUMME() (SUM in english) function. It does not summarize properly cells. It doesn't what the cell format is set to. So I decided not to create an own Bug Report as it might be the issue here.
Forgot to say LO 5.0.0.5 on Linux
Created attachment 117741 [details] SUM Func not working example.ods
I don't understand the last two Messages. As far as I understand, the last Part of the Formular shall write a "M" (for the "man-only" Chorus) in Columne "G" on the first side (Kasse). All other calculations of the formula are out of my understnding. I do not have a buddy, that understand Excel AND Calc to solve this issue. Karl
Hello Karl, I digged into your issue and found that LO doesn't support the {COUNT,SUM,AVERAGE}IFS starting from Excel 2007 onwards. Below you find an idea to substitute this by using SUMPRODUCT(). SUMIFS( range ; column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( range * column1>1 * column2<5 ) COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 * column2<5 ) AVERAGEIFS <==> SUMIFS/COUNTIFS My last two comment are for Miguel. Best regards Alex
Hi Alexander, 1. May be comments #13 and #14 are for other bug report. I can see the relation of attached file with this. 2. The SUMIFS COUNTIFS AVERAGEIFS are in LibreOffice at least from 4.2
1. May be comments #13 and #14 are for other bug report. I **can't** see the relation of attached file with this.
Hello Miguel, After a quick search for bugs related to lo5 I thought there might be a relation between this bug and my issue. So I will file a new report. Are you able to remove my attachment from this bug report? Regards Alex
off topic: can you point to the documentation where SUMIFS etc. is described? As I can see SUMIF is support not SUMIFS.
Hi Alexander, comment#18, sent a PM to you. I think it's pending write the help for those functions. https://bugs.documentfoundation.org/show_bug.cgi?id=64773#c9 But you can find it as any other function in the function wizard. The icon on the input bar to the left of the sum's icon.
(In reply to Alexander Nolting from comment #11) > Hello, > > I have an similiar issue with SUMME() (SUM in english) function. It does not > summarize properly cells. It doesn't what the cell format is set to. > > So I decided not to create an own Bug Report as it might be the issue here. Alex, I treid the following. On my Desktop (WIN7 Prof; LO 5.0.0.5) I opened a new Spreadsheet, set some cells to TIME (12:34h) and added some hours and Minutes to the Cells. Finally I pushed the SUM Button and the Summerization of the Cells worked fine. On my Laptop (WIN7 Prof, LO 4.4.4.3) I could veryfy this. There must be some Issue in the General Set Up of you Spread-Sheet I Gues. Karl
Hello Karl, after updating this morning to LO 5.0.0.5-324.3 from tumbleweed the sum func is working again. There are only a small glitch where the app doesn't recognize that a formula is added to the cell. This can be worked around by using the formula assistant that many time until the cell is been calculated by the formula. Thanks ;) ALex
(In reply to Alexander Nolting from comment #15) > Hello Karl, > > I digged into your issue and found that LO doesn't support the > {COUNT,SUM,AVERAGE}IFS starting from Excel 2007 onwards. Below you find an > idea to substitute this by using SUMPRODUCT(). > > > SUMIFS( range ; column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( range * > column1>1 * column2<5 ) > COUNTIFS( column1 ; ">1" ; column2 ; "<5" ) <==> SUMPRODUCT( column1>1 * > column2<5 ) > AVERAGEIFS <==> SUMIFS/COUNTIFS > > My last two comment are for Miguel. > > Best regards > Alex Hello LO Calc Supporters: I had the Oportunity to use Excel 2013 and saved the Spread-Sheet from Excel as xxx.ODS File. Opening this File (xxx.ODS) in LO (Desktop: WIN7 Prof-64Bit LO 5.0.0.5 and Laptop: WIN7 Prof-64Bit LO4.4.4.3) does NOT vreate any Error-Message. It seems to me, that the Spread-Shet is working perfektly under LO mow. The "Suspected Cell "Auswertung D28" does now as Formula show this Contend: "=SUMMEWENNS(Betrag;'file:///C:/Users/K-W.Hoffmann/Desktop/Kasse%20SV%202015.ods'#$$Konto;2110;VA;"M")" Is it possible, that the conversation of XLSX Format to the LO Format during th File-Opening? Tao all of you a nice Week-End. Karl
Hello Miguel, I got another update from tumbleweed again and the glitch where the cell is not calculated by a formula manually entered is also disappeared. So I'm fine now. Thanks to you both, Karl and Miguel. Alex
When opening file Kasse SV 2015_Test-Copy.xlsx in excel 2010 I get error "excel found unreadable content, recovery ... etc." After recovery is file opened. File Kasse SV 2015_Test-Copy.xlsx, Sheet Auswertungen, Cell D28 LO: =SUMIFS(Betrag;[0]!konto;2110;VA;"M") excel 2010: =SUMIFS(Betrag;'Kasse SV 2015_Test-Copy.xlsx'!Konto;2110;VA;"M") => the problem is in reference 'Kasse SV 2015_Test-Copy.xlsx'!Konto - it's reference to the same file. For example cell D29 contain formula =SUMIFS(Betrag;Konto;2110;VA;"G") and this formula is recognized in LO correctly (=SUMIFS(Betrag;Konto;2110;VA;"G")). Konto is named range =Kasse!$D:$D. To "fix" this file you can delete 'Kasse SV 2015_Test-Copy.xlsx'! - so D28 =SUMIFS(Betrag;Konto;2110;VA;"M") Setting as new / formula works in excel, doesn't work in LO.
The problem is that already the first document attached to this bug is saved by Calc with the broken formula expression part. To fix anything we'd need the original, presumably saved by Excel, document file.
I received the original confidential .xlsx file to reproduce. The good news is, I could not reproduce anything bad when using 5.0.6, 5.1.5 or 5.2.0 to open and save/reload the file. The bad news is, there's nothing to fix then.. In the original document I also don't see anything that could had produced the erroneous [0]!konto named reference. I assume this was a one time glitch with unknown cause, maybe happening only in one specific version. I can only close as worksforme.