Description: I used vlookup to copy values from other sheets. Although the same formula is applied throughout the column, some values are not correct. If I reapply the same formula, it shows the correct value. If I make some changes in the sheet being used by vlookup, it changes the incorrect value to correct ones. The file exhibiting this behavior contains sensitive information. If those information are removed(change data), the formula calculation is triggered by changes and incorrect values are replaced by correct values. However, if I do not save and close the file and reopen it, the incorrect values are shown every.single.time. I have screencasted this behavior and it is available here for viewing for getting a clearer idea. https://www.youtube.com/watch?v=4oakCHd6FOQ&feature=youtu.be I am really sorry that I can not share this file due to the information it contains. I am stating it as severe, since such errors in data calculation completely throwsthe usefulness of libreoffice out of the window. Please do not think that I am not happy with LibreOffice. I really am, and I will provide any information necessary so that you can fix it. Steps to Reproduce: Only a few values are incorrect, and I am not sure what triggers it. Actual Results: Incorrect result from formula. Expected Results: Correct value taken from other sheets using vlookup. Reproducible: Couldn't Reproduce User Profile Reset: No Additional Info: Kubuntu: 16.10, 64 bit. User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/57.0.2987.98 Safari/537.36
Hello Amit, Thank you for reporting the bug. Unfortunately I can't reproduce in Version: 5.4.0.0.alpha0+ Build ID: 36afb355ac37122d32d624db079def123ef548a2 CPU Threads: 4; OS Version: Linux 4.8; UI Render: default; VCL: gtk3; Locale: ca-ES (ca_ES.UTF-8); Calc: group Could you please try to reproduce it with a master build from http://dev-builds.libreoffice.org/daily/master/ ? You can install it alongsidethe standard version. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the master build
Hi Xisco, Thank you for your response. I am downloading master~2017-03-07_02.52.26_LibreOfficeDev_5.4.0.0.alpha0_Linux_x86-64_deb.tar.gz right now, and will inform as soon as possible. Just opening the file containing the incorrect value and checking if the formula is applied properly should suffice, right?
Hi Amit, Could you also attach the document to reproduce the problem?
Hi Xisco, Really can't. It contains sensitive information. Any attempt to remove any information by deleting/modifying somehow refreshes the affected sheets and shows the corrected values.
Hi Xisco, I installed master~2017-03-07_02.52.26_LibreOfficeDev_5.4.0.0.alpha0_Linux_x86-64_deb.tar.gz , opened the spreadsheet file(.xlsx extension, in case that is necessary information) using LibreOffice Version: 5.4.0.0.alpha0+ Build ID: a5a6694128728c48c1a8482450a21ad49025c40c CPU threads: 4; OS: Linux 4.8; UI render: default; VCL: kde4; TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-03-07_02:52:26 Locale: en-US (en_US.UTF-8); Calc: group. The incorrect value for correct formula is still present.
(In reply to Xisco Faulí from comment #3) > Hi Amit, > Could you also attach the document to reproduce the problem? Xisco, is it okay to remove the sensitive information and upload it? I should mention that any modification to sheets "refreshes" the values by recalculating the formula in the sheets. From the uploaded file, it might not be possible to view the values being refreshed, but it might give some idea.
(In reply to Amit Seal Ami from comment #6) > (In reply to Xisco Faulí from comment #3) > > Hi Amit, > > Could you also attach the document to reproduce the problem? > > Xisco, > is it okay to remove the sensitive information and upload it? I should > mention that any modification to sheets "refreshes" the values by > recalculating the formula in the sheets. From the uploaded file, it might > not be possible to view the values being refreshed, but it might give some > idea. it's ok as long as the bug can be reproduced with the new data, otherwise it's not really helpful...
Created attachment 132012 [details] Bug affected sample file The uploaded ods was created by: - unzipping the original bug affected file, - removing the sensitive information by editing the content.xml - zipping back the contents (following this link: http://stackoverflow.com/questions/4957212/how-does-open-office-compress-its-files) - opening and repairing the new file - saving it as a new ods file I checked and the new file is also affected by the bug.
(In reply to Xisco Faulí from comment #7) > (In reply to Amit Seal Ami from comment #6) > > (In reply to Xisco Faulí from comment #3) > > > Hi Amit, > > > Could you also attach the document to reproduce the problem? > > > > Xisco, > > is it okay to remove the sensitive information and upload it? I should > > mention that any modification to sheets "refreshes" the values by > > recalculating the formula in the sheets. From the uploaded file, it might > > not be possible to view the values being refreshed, but it might give some > > idea. > > it's ok as long as the bug can be reproduced with the new data, otherwise > it's not really helpful... Hi Xisco, After trying for a few hours by trying to modify the contents programmatically (python, pyexcel_ods, and other libraries) I finally was able to remove the sensitive data by editing the content.xml directly after unzipping. I checked and the file is affected by the aforementioned bug. I opened it using both LibreOffice 5.3.0.3 and 5.4.0.0.alpha0+. I hope this will be useful. Now, I leave the rest to you, as I gotta take some rest. (ouch, my back hurts) :D
Could you please mention the steps to reproduce it with the new file ? better to have a list of steps than a video
(In reply to Xisco Faulí from comment #10) > Could you please mention the steps to reproduce it with the new file? > better to have a list of steps than a video I am not sure how it can be reproduced. I've been using LibreOffice for years, and this is the first time something like this happened which only affected a few cells. I simply used vlookup to refer values from one sheet to another the usual way. Later after printing and checking, - I suddenly found that the values being shown were not correct, even though the formula was right. The formulas were applied throughout the columns by double-clicking the cell handler after writing it once. I provided the file as a sample so that the existence of the bug can be confirmed. I am providing further details so that it is easier to understand what is being referred where. 1. The 2ndTrimester sheet contains data related to results, first column Roll is going to be used as lookup index in vlookup function. 2. The Detailed Sheet is using IFERROR(VLOOKUP()) to get values from 2ndTrimester sheet. It is looking at the roll from the first column, using it to lookup a particular row in A22 to O60 range in 2ndTrimester Sheet, if matched - it is taking the value from the specified column, if there is any error, it is replacing the value with 0. 3. For example, check A16 in Detailed Sheet. The J16 contains the following formula: =IFERROR(VLOOKUP($A16,$2ndTrimester.$A$22:$O$60,5,0), 0) for 1612, the value being of A16, the value of $2ndTrimester.$A$22:$O$60 is 3.75. However, the value being shown at J16 is ... 0. The same goes for K16, L16, M16, J17, K17, M17, J18, K18, L18, M18, J19, K19, L19, M19. An interesting observation is that the bug affected columns seemingly shifted a row. Here is what I mean shown in this picture: http://imgur.com/a/Hxdq3 I hope this will be informative enough.
I confirm that it needs a hard recalc (Ctrl-Shift-F9). After that and saving & reloading, the values stay. There have been some vlookup recalc bugs: bug 61662 (but it went away). Not sure what can be done about this. Arch Linux 64-bit, KDE Plasma 5 Version: 5.4.0.0.alpha0+ Build ID: 54f9a81a1c041c18d209d7eb4659ac18a3ca76dd CPU threads: 8; OS: Linux 4.10; UI render: default; VCL: kde4; Locale: fi-FI (fi_FI.UTF-8); Calc: group Built on March 25th 2016
(In reply to Buovjaga from comment #12) > I confirm that it needs a hard recalc (Ctrl-Shift-F9). After that and saving > & reloading, the values stay. > > There have been some vlookup recalc bugs: bug 61662 (but it went away). > > Not sure what can be done about this. > > Arch Linux 64-bit, KDE Plasma 5 > Version: 5.4.0.0.alpha0+ > Build ID: 54f9a81a1c041c18d209d7eb4659ac18a3ca76dd > CPU threads: 8; OS: Linux 4.10; UI render: default; VCL: kde4; > Locale: fi-FI (fi_FI.UTF-8); Calc: group > Built on March 25th 2016 Hi Buovjaga, Thank you for the tips. I didn't know about the shortcut. I will definitely be using this shortcut every time before pressing ctrl+s. Can not there be a feature as an option that will hard recalc the sheet when the file is closed? Doing so by default will hit performance understandably.
(In reply to Amit Seal Ami from comment #13) > Can not there be a feature as an option that will hard recalc the sheet when > the file is closed? Doing so by default will hit performance understandably. The developers want to keep it as a development/debugging feature.
(In reply to Buovjaga from comment #14) > (In reply to Amit Seal Ami from comment #13) > > Can not there be a feature as an option that will hard recalc the sheet when > > the file is closed? Doing so by default will hit performance understandably. > > The developers want to keep it as a development/debugging feature. so... is this a confirmed bug? Anything I can do here to help?
The document contains cached values and hard recalc helps with that. Here is an example of a similar case: https://bugs.documentfoundation.org/show_bug.cgi?id=82135#c18 I will close this as notabug just like that other.
Dear Buovjaga, Sorry to revive this dead thread again; but this bug hit me again. Since I am in academia, this is a issue that affects the grade of students if the values are not hard recalculated every time. I have went through #82135, and the statement was: "The mentioned behaviour here is not a bug. We are using cached values and if the file contains wrong values there is nothing we can do." However, I have to disagree. For a file that was created, edited and maintained only using LibreOffice Calc, if the cached values are wrong - it should be a problem of LibreOffice, and not the file itself. At least the "Always Recalculate" should be turned on by default in LibreOffice Calc.