Created attachment 100608 [details] Up: select cell with formula and type Ctrl-Shift-F9 => Err:522 in this cell and "0" and several other cells; down: select empty cell and type Ctrl-Shift-F9 => everything is correct. Problem description: The problem happens in a complex spreadsheet (accounting software), with several macros (in Star Basic) using references to large ranges of cells. Sometimes, the cell or the line which is selected is not correctly recalculated, instead it displays "N/A" or "Err:522" Steps to reproduce: Method 1: 1. Open the file 2. Select another sheet of the file Method 2: 1. Type Ctrl-Shift-F9 for "unconditional recalculate" Method 3: 1. Select a cell (X) in the sheet 2. Go to another sheet of the same file 3. Modify a cell, which has an effect onto X 4. Select back the sheet that contains cell X Current behavior: The cell which is selected contains "Err :522". Other cells in the same sheet my contain "0" or "N/A" instead of their current value. The workaround is to select a cell with no formula and type Ctrl-Shift-F9. Often, the calculation time is also very long when one cell is modified, which may indicate that all cells have been recalculated, not just the cells that depend directly or indirectly on the modified cell. See screenshoots: #1: a cell with a formula was selected when Ctrl-Alt-F9 was typed in. Results: "Err:522" in this cell and "0" in many other cells. This is reproducible. #2: an empty cell was selected when Ctrl-Alt-F9 was typed in. Results: everything is correct. This is reproducible too. I may transmit the whole file if required, but only if this is useful, since it must first be anonymized. Expected behavior: When a cell is modified, all the cells that depend directly or indirectly on it should be recalculated. The other cells should not be recalculated, so the computation time should be short when just one cell is modified. The cells in other sheets should not be recalculated until the sheet is selected or the file is saved, unless they have an indirect effect onto the current sheet. When Ctrl-Shift-F9 is typed in, all the cells should be recalculated. No cell should contain "Err:522", or "0" or "N/A" when this is not the correct value. Operating System: Ubuntu Version: 4.2.4.2 release
Hi Miguel, thanks for reporting. Please can you attach a sample file?
Created attachment 101021 [details] Sample file showing the problem I have just removed from the file any information that cannot be disclosed. In order to experience the bug, you can do the following: 1) go to sheet "Bal", select cell K15, and type Ctrl-Shift-F9 (uncond. recalc) Result = problems: - cell K15 contains "Err :522" - column G, lines 8 through 25: each cell contains "0" (these cells contain a formula that links to cells in sheel "Plan", and these cells contains "Err :522") 2) go to sheel "Bal", select cell A1, and type Ctrl-Shift-F9 Result = OK: - cell K15 contains the correct value (1632.19) - column G, lines 8 through 25: accounting labels in French (e.g. G8 is "Fonds associatif sans droit de reprise") Error 522 means "circular reference". In my options, the circular references are not allowed.
I can't reproduce the issue. Version: 4.2.4.2 Build ID: 63150712c6d317d27ce2db16eb94c2f3d7b699f8 No matter where I go to do hard recalc, sheet "Bal" never shows errors. Pls try resetting the user profile, sometimes resolves strange issues. https://wiki.documentfoundation.org/UserProfile
@m.a.riosv: It is somewhat different now that I reopen the file. No need to precise that I must first need to add the file location in the trusted sources for Basic macros. Ctrl-Shift-F9 with cursor on Bal/K15 => yes, cell K15 is still OK, but: 1) column G contains all "0"s, this is an error. 2) After that, in sheet "Plan", all the cells in B8:C106 contain "Err :522". Ctrl-Shift-F9 with cursor on Bal/A1 => K15 is still OK and there are no errors: 1) column G contains French texts (G8="Fonds associatif sans droit de reprise") 2) in sheet "Plan", cells in B8:C106 contain no error (B8="102", C8="Report à nouveau (solde créditeur)") I suspect that there is a problem related to the cell names, let me precise something: - The cell range Plan.B7:C106 is named "Plan", and some errors seem to be tracked down to formulae using this range. - Some formulae use the name "Plan" and are wrong (value "#N/D") when the problem happens. For example, cell AD.F9 contains: =SI(E9="";"";RECHERCHEV(E9;Plan;2;0)) in English probably something like =IF(E9="","",VLOOKUP(E9,Plan,2,0)) - When I click on Insert / Names / Manage (Ctrl-F3), no name appears, even when the cells range is selected. However, the name does exist, I can check it by entering the following formulae: =ROW(Plan) =ROWS(Plan) =COLUMN(Plan) =COLUMNS(Plan) =SHEET(Plan) =SHEETS(Plan) - I get no improvement when I replace all occurrences of name "Plan" by the absolute reference "$Plan.$B$7:$C$106" - However, I cannot remove the name. It has the name of one of the sheets, but there is also no improvement when I rename the sheet "Plan" to another name. Thank you for your help.
(In reply to comment #4) > @m.a.riosv: > Ctrl-Shift-F9 with cursor on Bal/K15 => yes, cell K15 is still OK, but: > 1) column G contains all "0"s, this is an error. (1) Ok for me, I think they are account names. > 2) After that, in sheet "Plan", all the cells in B8:C106 contain "Err > :522". (2) Ok for me, accounts with their names. > Ctrl-Shift-F9 with cursor on Bal/A1 => K15 is still OK and there are no > errors: > 1) column G contains French texts (G8="Fonds associatif sans droit de > reprise") Ok, like in (1) > 2) in sheet "Plan", cells in B8:C106 contain no error (B8="102", > C8="Report à nouveau (solde créditeur)") Ok. like in (2) B7="102" C8 ok. > > I suspect that there is a problem related to the cell names, let me precise > something: > > - The cell range Plan.B7:C106 is named "Plan", and some errors seem to be > tracked down to formulae using this range. There is no cell names. > > - Some formulae use the name "Plan" and are wrong (value "#N/D") when the > problem happens. For example, cell AD.F9 contains: > =SI(E9="";"";RECHERCHEV(E9;Plan;2;0)) > in English probably something like > =IF(E9="","",VLOOKUP(E9,Plan,2,0)) Works fine for me. > > - When I click on Insert / Names / Manage (Ctrl-F3), no name appears, even > when the cells range is selected. However, the name does exist, I can check > it by entering the following formulae: ...... > - I get no improvement when I replace all occurrences of name "Plan" by the > absolute reference "$Plan.$B$7:$C$106" > > - However, I cannot remove the name. It has the name of one of the sheets, > but there is also no improvement when I rename the sheet "Plan" to another > name. Ranges are database ranges Menu/Data/Define ranges
Thanks, I now understand about the range names and database names. But replacing the database name by the range name does not improve anything (but deleting a database range makes the software crash reproducibly if this range is still used in formulae). Setting the GUI in English does not change anything either. I am using the same build as you are, what else can I do to confirm the bug? May it be platform-specific? I have Linux 32 bits: Linux ....... 3.13.0-30-generic #54-Ubuntu SMP Mon Jun 9 22:47:59 UTC 2014 i686 i686 i686 GNU/Linux Let me explain in which conditions the problem appears: it is written in a complex way, in order to recalculate the macros just when it is needed (and it used to work well). There are 2 ranges, filled manually: $Plan.$E$7:$G$526, $Plan.$I$7:$K$526 The references to these ranges are set in two cells: cfg.C33, cfg.C34 The text address of these ranges are in two next cells: cfg.D33, cfg.D34 The locations (sheet, sheet number, row, row number, column, and column number) of these ranges are in two blocks of 6 cells: cfg.E33:J33, cfg.E34:J34 The 2 cells Plan.B7:C7 are a matrix, computed by a macro using the locations of these ranges. They are always good. The 2 cells below Plan.B8:C8 are exactly the same matrix, computed in exactly the same way, except that one parameter is "1" instead of "2". Depending on where is the cursor when I recalculate, these cells (and all the cells below) will contain "Err:522" (circular reference) instead of the correct result. Although this is complex, I can see no circular reference in this process. At some date, probably in 2011 or 2012, the recalculate behavior evolved: it became must slower (systematic complete recalculate?) and less reliable (this kind of problems). Miguel
Sorry I can't help, what I think you can do is follow the error to find out where it appears.
Created attachment 101093 [details] Minimal test case This is a simplified test case, which a simple structure, and no function returning a matrix. Select cell B14 in sheet "Plan". Type Ctrl-Shift-F9 (unconditionnal recalculate). In approx. 50% of the cases, I get "Err:522" (circular reference) in the 6 cells B7:D8: not only on the line where is the cursor, but also on the previous line (which is independant). Strangely enough, cells D13 and D14 are also "Err:522" (circular reference), although they are calculated from a simple formula that uses just the content of a string in cell D6. This cell does exist: its content is used by the rest of the column.
Created attachment 101094 [details] Screenshot of the previous test case after Ctrl-Shift-F9
Created attachment 101095 [details] More simplified test case Test case with just one sheet. Using Tools / Detective / Show precedents (Shift-F7), it is quite obvious that there is no circular reference. Selecting H13 and typing Ctrl-Shift-F9 (uncond. recalculated), cells F13, H13, I13 get "Err:522" (circular reference), not always but in more than 50% of the cases.
Created attachment 101096 [details] Screenshot of last test-case
If I push in continuous [Ctrl+Shift+F9] with your last sample, I can see for an instant the error, but never ends with it. Maybe it's an intermediate state along macro calculations.
Created attachment 101104 [details] Stripped-down again; just 1 macro, called just once; type Ctrl-Shift-F9 on E13 I don't think it has to do with an intermediate state of a macro. I have stripped-down the file even more. Now, just one macro is called (QSortRef). When I press Ctrl-Shift-F9 on E13 and get "Err:522", I can check with the Basic debugger that: - the macro is called just once - the function exits normally and with a correct value (string of 24 characters)
This bug might be related to another "recalculate" bug with macros, which I confirm and have also found independently: https://bugs.freedesktop.org/show_bug.cgi?id=43003
Then would be better resolve this as duplicate of that one, putting there your comments.
Not sure it's really a duplicate! The symptoms are quite different and the test cases too. The other bug seems to happen only with matrix and the cells will keep the old results; the current bug can happen without a matrix and the cells display either the updated result or an error, never the old result. Just there are some similarities (recalculate problems + macros).
I have no problem on Linux 4.3.3 or Windows master from today. Version: 4.3.2.2.0+ Build ID: 4.3.2.2 Arch Linux build-1 and Win 7 64-bit Version: 4.4.0.0.alpha1+ Build ID: b7d8a58ff2698ffc6e22943f64aa97c5ea253bd9 TinderBox: Win-x86@42, Branch:master, Time: 2014-11-05_00:40:38
I still have lots of problems related to "cell not recalculated or badly calculated when it is selected, or in the same raw or column as the selected cell". On the last attachment, I can reliably reproduce the bug, with version 4.3.3.2 (Linux 32 bits). Procedure: 1) get attachment: https://bugs.freedesktop.org/attachment.cgi?id=101104 2) open it (with macros enabled) 3) select cell A13 4) type "x" into cell, but DO NOT PRESS ENTER 5) select cell H13 Result (error): Cell E13 contains "Err :522" Expected result: Cell E13 should countain "9" Another simpler bug, also related to recalculation algorithm with macros, is also still there: https://bugs.freedesktop.org/show_bug.cgi?id=43003
Adding self to CC if not already on
(In reply to Miguel from comment #18) > I still have lots of problems related to "cell not recalculated or badly > calculated when it is selected, or in the same raw or column as the selected > cell". > > On the last attachment, I can reliably reproduce the bug, with version > 4.3.3.2 (Linux 32 bits). > > Procedure: > 1) get attachment: https://bugs.freedesktop.org/attachment.cgi?id=101104 > 2) open it (with macros enabled) > 3) select cell A13 > 4) type "x" into cell, but DO NOT PRESS ENTER > 5) select cell H13 > > Result (error): > Cell E13 contains "Err :522" > > Expected result: > Cell E13 should countain "9" > > I can confirm with Version: 4.5.0.0.alpha0+ Build ID: 88562ee6e352b5446bb55e906e8f1c2f34035a49 TinderBox: Linux-rpm_deb-x86_64@46-TDF, Branch:master, Time: 2015-01-16_23:58:11 After hard recalc (CTRL+SHIFT+F9) is result correct. I can not reproduce with formula where custom macro formula is not involved and therefor I mean that this bug is duplicate of bug 81757. Miguel, please take a look at bug 81757 if you agree. I've set the bug as duplicate, set again to unconfirmed if you don't agree. Thanks *** This bug has been marked as a duplicate of bug 81757 ***
(In reply to raal from comment #20) Raal, I have no idea, but it is quite possible indeed that this is a duplicate. They both imply calculation with macros. When this one is fixed (hopefully), I will check again with the other one.
this bug looks in any way solved / gone with actual versions (6.4.0.2 winx64), while the duplicate 81757 is still new and - checked with above version - unresolved, this bug was about fails in recalculating the actual selected cell, 81757 is about a 'lagging' in iterative calculations, it may be that they are linked in deeper layers of the code, on the surface - the user interface - they are quite different, thus i'd suggest to remove the duplicate relation between these two bugs, and recheck if this is solved and mark it accordingly. reg. b.