Download it now!
Bug 71598 - Other: Cell Not Recalculating
Summary: Other: Cell Not Recalculating
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.1.2.3 release
Hardware: Other Linux (All)
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: BSA target:4.3.0 target:4.2.0 target:...
Keywords:
Depends on:
Blocks:
 
Reported: 2013-11-14 10:06 UTC by John McCulloch
Modified: 2014-01-27 09:43 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Test spreadsheet with macro (19.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-15 09:10 UTC, John McCulloch
Details
Data file to use with macro. (382 bytes, text/plain)
2013-11-15 09:17 UTC, John McCulloch
Details
Simplified version (18.62 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-11-16 10:42 UTC, John McCulloch
Details
Test case which shows the recalc bug (25.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2013-12-12 11:55 UTC, Jeff H
Details
Better test case which shows the recalc bug (15.40 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-01-15 08:00 UTC, Jeff H
Details

Note You need to log in before you can comment on or make changes to this bug.
Description John McCulloch 2013-11-14 10:06:06 UTC
Problem description: 
Cell referencing formula on another sheet does not recalculate.  Cell A is a cell which has a formula with "SUM(range)".  Cell B is on another page and references cell A.  When a macro adds a row into the range used in Cell A, Cell A recalculates automatically, but Cell B does not.

Steps to reproduce:
1. Make a column of numbers and below it make a cell with =SUM(R), where R is a range of the column of numbers.  This SUM cell will be cell A.
2. On another sheet make a cell with = X + Y, where X is some cell on the same sheet and Y is a reference to cell A created in step 1.  This new cell will be cell B. 
3. Make a macro which adds a row into range R and put a number in the column in that range.
4. Run the macro.

Current behavior:  Cell A updates and recalculates.  Cell B does not recalculate.

Expected behavior:  Both A and B recalculate.

              
Operating System: Debian
Version: 4.1.2.3 release
Comment 1 Joel Madero 2013-11-15 02:18:50 UTC
Please provide a test case with the macro. 

Marking the bug as NEEDINFO, once you attach a sheet please mark as UNCONFIRMED and we will check it out.
Comment 2 John McCulloch 2013-11-15 09:10:45 UTC
Created attachment 89253 [details]
Test spreadsheet with macro

Use this with attached data file.
Comment 3 John McCulloch 2013-11-15 09:17:29 UTC
Created attachment 89254 [details]
Data file to use with macro.

Open attached test.ods.

Note value of Regular_Cash.I33 and Regular.I19.

Click button on sheet Main.  Select Regular and Transactions and click Browse and find this file.  Click OK.

Note cell Regular_Cash.I33 moved down one, expanded range and recalculated.

Note that cell Regular.I19 did NOT recalculate.
Comment 4 John McCulloch 2013-11-15 09:19:08 UTC
Open spreadsheet, save data file and run macro with button.  See Details on data file attachment.
Comment 5 Joel Madero 2013-11-15 21:58:53 UTC
That test file fails on my machine, 

BASIC runtime error.
Sub-procedure or function procedure not defined


Additionally, if at all possible it would be much nicer to have a simpler test case (if that's not possible it's okay, just makes our job a ton easier). 

Marking again as NEEDINFO as I can't test the document since it fails
Comment 6 John McCulloch 2013-11-16 10:42:48 UTC
Created attachment 89311 [details]
Simplified version

What you got is probably a result of leaving Positions selected instead of selecting Transactions.  This case does not require radio buttons at all.

The test data is the same.
Comment 7 Jeff H 2013-12-12 11:55:55 UTC
Created attachment 90661 [details]
Test case which shows the recalc bug

I'm not sure this is the same bug, but the attachment LORecalcBug.ods clearly demonstrates a recalc bug in LO 4.1.3.2 (but works fine in LO 4.0.5.2). Open the spreadsheet, enable macros, go to the CashOut page (make sure "1" is in the #OUT column of the "1,000" row, and others are blank), click on the Process Disbursement button and you should get a MsgBox that indicates the formula error. Now go to the Journal sheet and the ending balance will be some formula like "=J3+F19-G18", but if you do the math, it won't be right. It should be the same as the bottom of the balance column there. Also if you go to the Balance sheet, you will see the difference there as well. If you type Ctrl-Shit-F9 on any of these sheets, it recalculates successfully and the error goes away.

Sorry I didn't strip the code down more. As it is it took me a couple of hours to strip it down this far. Hope this helps find the bug - it's a bad one for us, and prevents us from upgrading to 4.1.
Comment 8 Jeff H 2014-01-09 16:15:25 UTC
It has been confirmed that this bug still exists in the 4.1.4 release. It doesn't appear from this page that anything is being done to address this bug. How do I make sure that it is brought to the right person's attention? Can I change the status to New, since I provided a test case which shows the bug? Or does someone else need to do that?
Comment 9 Joel Madero 2014-01-09 16:42:08 UTC
Please do not move to NEW - we need independent confirmation of every bug. 

As for raising it to the proper person, until it's marked as NEW, nothing more to do be done (I am terribly busy right now, will try to remember to get to it tonight). This isn't a terribly high priority bug to be honest (if you look through our list of bugs you'll get a sense of why).

If you want to do something more which would be tremendously helpful you can try to bibisect it:

https://wiki.documentfoundation.org/QA/HowToBibisect

This is only useful if it's a regression, if the macro works in 3.6 but not 4.0, try the bibisect40 package, if it works in 4.0 but is broken at some later point (4.0.+ - 4.1.+) try the other package listed that includes 4.0 builds. 

If this isn't a regression, even less likely it'll be fixed any time soon
Comment 10 John McCulloch 2014-01-10 11:00:55 UTC
(In reply to comment #9)
> Please do not move to NEW - we need independent confirmation of every bug. 
> 
> As for raising it to the proper person, until it's marked as NEW, nothing
> more to do be done (I am terribly busy right now, will try to remember to
> get to it tonight). This isn't a terribly high priority bug to be honest (if
> you look through our list of bugs you'll get a sense of why).
> 
> If you want to do something more which would be tremendously helpful you can
> try to bibisect it:
> 
> https://wiki.documentfoundation.org/QA/HowToBibisect
> 
> This is only useful if it's a regression, if the macro works in 3.6 but not
> 4.0, try the bibisect40 package, if it works in 4.0 but is broken at some
> later point (4.0.+ - 4.1.+) try the other package listed that includes 4.0
> builds. 
> 
> If this isn't a regression, even less likely it'll be fixed any time soon

This bug does not exist in 4.0.6.2.  That's what I'm running now and I don't have the problem.
Comment 11 Jeff H 2014-01-15 08:00:37 UTC
Created attachment 92120 [details]
Better test case which shows the recalc bug

This test case has been cut down to the absolute minimum which shows the bug. You open the .ods file, a message tells you that the ending balance formula is correct. You click the Process Disbursement button and a message tells you that the ending balance formula is NOT CORRECT. You can click on the Journal tab to verify for yourself. The ending balance is just a formula which is the difference between 2 cells, but the difference is incorrect. If you type Ctrl-Shift-F9 to recalculate all formulas, it updates and is correct again. The macro that runs when you click the button is very simple. It inserts a row (above the Total disbursements line) and then increases the debit amount by 1000. This leads to the bug every time (in LO 4.1.3.2, and a colleague saw it in 4.1.4 as well).

Here are the observations I have made:
If I remove the insertion of the row, there is no bug.
If I insert the row AFTER the Total disbursements sum line, there is no bug.
If the ending balance is calculated directly from the debit amount, no bug.
If the Total disbursements is not a sum, but just a number, there is no bug.
If the macro makes the Journal sheet active before the operations, no bug.
If I move the button to the Journal sheet, there is no bug.
If I run the macro from the Journal sheet, there is no bug.
If I run the macro from the CashOut sheet, THE BUG DOES SHOW UP.

So in summary, I think the bug shows up when a macro makes changes to a non-active sheet so that a sum formula is pushed down by an insert row operation.
Comment 12 Jeff H 2014-01-15 08:08:40 UTC
I hope that this new test case (and my observations, above) will help in the process of tracking down and eliminating this bug. In making the observations, I believe I came up with a reasonable work-around - make the Journal sheet active before inserting the row for the new data. Other people who face this bug may be able to use that same idea to avoid this bug for now.

I didn't try the bibisection because I am mostly working on Windows at the moment, but I believe my observations should be able to help track the bug down fairly accurately for anyone who cares to try to find it.
Comment 13 Eike Rathke 2014-01-23 10:41:20 UTC
Taking.
Comment 14 Commit Notification 2014-01-23 21:48:28 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=20b7476142f75b49d10a75e48429a94cff0cec32

resolved fdo#71598 postpone SetDirty during Insert/Delete



The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 15 Eike Rathke 2014-01-23 23:23:25 UTC
Pending review
for 4-2 at https://gerrit.libreoffice.org/7623
for 4-1 at https://gerrit.libreoffice.org/7624
Comment 16 Commit Notification 2014-01-27 09:42:00 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-2-0":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a0f1ed22b457586ef1506b071266af5d0fa28ddb&h=libreoffice-4-2-0

resolved fdo#71598 postpone SetDirty during Insert/Delete


It will be available already in LibreOffice 4.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.
Comment 17 Commit Notification 2014-01-27 09:43:22 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-4-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=b99a2691484e9b0533530ad4f02e15ad4031b74b&h=libreoffice-4-1

resolved fdo#71598 postpone SetDirty during Insert/Delete


It will be available in LibreOffice 4.1.6.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds
Affected users are encouraged to test the fix and report feedback.