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
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.
Created attachment 89253 [details] Test spreadsheet with macro Use this with attached data file.
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.
Open spreadsheet, save data file and run macro with button. See Details on data file attachment.
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
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.
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.
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?
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
(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.
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.
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.
Taking.
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.
Pending review for 4-2 at https://gerrit.libreoffice.org/7623 for 4-1 at https://gerrit.libreoffice.org/7624
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.
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.