(Note: the actual LO version I'm using is 4.2.2-2, installed on Arch Linux/xfce with pacman -Syu yesterday, 3/19/2014.) I opened a Calc file that has long been in use today and a particular set of formulas displayed "zero" as their cell values. The spreadsheet is a collection of my financial records with each line containing a transaction; the main sheet (essentially general journal) has about 11,000 rows and 7 columns of data. The errant formula, running throughout column G, contains: =YEAR(C11058)&F11058 Column C contains a date; column F contains an account code. The result of the formula should be text of the form "2014ent". While the formulas were all correctly intact, each column G cell displayed a zero. Further, and I don't know if this is a separate problem, another sheet (general ledger) evaluates the cells from the journal sheet and puts SUMIF results on a layout with accounts in rows and years in columns. Unfortunately, the cells on this sheet have lost their references to the journal sheet. A typical formula here now reads: =SUMIF( $G$5:$G$13681,E$4&$B12, $E$5:$E$13681) Note the presence of a space and the absence of a sheet name at the beginning of the first and third parameters. ---------------- I have periodically had a similar problem in another, much simpler, spreadsheet file. This one consists of a single sheet that produces a two-page paper report. I have various text titling information in cells A1, A2, and A3 for the first page and then I have formulas which reproduce that titling text for the second page as follows: A42: =A1 A43: =A2 A44: =A3 The past behavior, though sporadic, has shown the correct formula in the page-two cells, but displayed zeroes. Just now though, the sheet shows the correct text in A42, but shows blanks in A43 and A44. ---------------- In both of these spreadsheets I can display the values for the simple formulas (=YEAR(C11058)&F11058 and =A1) by a hard recalculation with Ctrl-Shift-F9. This does not, however, correct the sheet name deletion in the SUMIF formulas. This hard-recalc solution was suggested on http://ask.libreoffice.org/en/question/31185/after-updating-to-libre-office-4211-calc-stopped-performing-correctly/ I tried reproducing the problem in a new file, but am unable to. I am reluctant to upload either of my two problem files for review since they both contain private and sensitive data. I am happy to provide whatever other information I can and test potential solutions. Please let me know what I can do to help resolve this issue. Many thanks.
You could send me the problematic file attached to an email. You may OpenPGP encrypt the mail and attachment using my key 0x65632D3A - 2265 D7F3 A7B0 95CC 3918 630B 6A6C D5B7 6563 2D3A http://pool.sks-keyservers.net:11371/pks/lookup?search=0x6A6CD5B765632D3A&op=get
Created attachment 96207 [details] Example Problem File Eike, thanks for the offer to privately email you the files, but I was able to remove the sensitive information from the smaller (and less complicated) file, uploaded herewith. There is a slight difference in the behavior of this file from my original description. Now, cells A42, A43, and A44 show zeroes as their formula results. This is consistent with the (incorrect) behavior in my financial file. Many thanks for taking a look at this.
Created attachment 96208 [details] Example Screenshot Attached is a schreenshot showing the problematic result. Note also that Help > About identifies Calc as v.4.2.2.1, although pacman -Q says I have v.4.2.2-2 installed.
Hi C A J, thanks for reporting. There is an option for recalculate xlsx files when opening in: Menu/Tools/Options/LibreOffice calc/Formula/Recalculation on file load - Excel 2007 or newer. This option works fine with your file. Also a hard recalc [Ctrl+Shift+F9] solves the issue. I think not a bug, but I hope Eike can confirm.
Thanks for the suggestion on setting recalc-on-file-load. That solves the problem for the two spreadsheets that were misbehaving. I'm just curious, though. Is recalc-on-file-load a new option? (Or a config parameter reset by v.4.2.2-2?) The problem with the file I sent has been intermittent and the problem with the 11,000-row file only occurred for the first time last week.
It was a new option in 4.0. I think the intention was to allow avoid long recalculations at the opening.
Thanks all 'round. I went ahead and closed out #76411.