Created attachment 123936 [details] named-expression-bugs.ods I have a Calc spreadsheet where I can no longer define a name for a range without incurring miscalculations across all sheets. Possibly related, I am also having issues with column heading literals (unsure the correct jargon here) used in formulas. The testing steps below are concerning the attached ODS document (the names and figures are bogus) and LibreOffice Calc 5.1.1.3 on OS X 10.10.5 Yosemite, though the bug(s) are also present in 5.0.x. Upgrading to 5.1.x did not solve these issues. 1. Open attached ODS document. 2. Select the sheet named Vitamins. 3. Notice the miscalculations for the two total columns. Recalculate does nothing; one must re-enter the formula (click the Function '=' control twice) to trigger a recalculation, which is typically lost between document close and reopen. 4. Select the sheet named Salary. If the document was correctly interpreted on open, the values in rows 5 and 6 will differ. 5. Select the sheet named Budget. Notice the #NAME? errors beginning at R27C6. IncomeGrossP2 is in fact defined, but improperly as budget!R[24]C[17] instead of Budget!R25C18 in the Manage Names dialog. References to this expression do not function properly. No amount of editing or deleting and recreating this expression seems to work. 6. (May need to close and reopen the document to trigger this step.) Select row 53 in its entirety by clicking its row number. In the Name Box, attempt to define a new name by typing "NonTaxableIncome". The name will be defined improperly similar to that in step #5, and will not function. Additionally, data in the section "Income (P1)" will be incorrectly mapped, causing other sections like "Taxes (P1)" to be incorrectly calculated as well; see step #7 for the cause. 7. Select the sheet named Salary. Notice that the data in rows 5 and 6 are equivalent. Select R6C7 and perform Tools > Detective > Trace Precedents. Notice that the selected cell pulls data from the wrong row. 8. Re-enter the formula for the cell by clicking the Function '=' control twice. The correct value is calculated using the correct row data and the trace now appears correct. Repeat this formula recalculation for all cells with formulas in row 6. The sheet should now be correct again. 9. Select the sheet named Budget. Notice how step #8 fixed the calculations, but any further attempts to fix the previous named expression issue on this sheet will re-trigger the Salary sheet issue. As you can see, this is an endless loop of one issue causing another, rendering the document unusable in this state. If any further information is needed, I will gladly assist. Cheers.
Updating version as OP says it was present in 5.0.x (version is earliest version where problem exists)
Hi @Bradley, 3) I think this is a duplicate of bug fdo#93894 5) Editing IncomeGrossP2 to have the right absolute address, then it seems to work properly. With a relative address, reference change relatively to the cell on where it is. 6) Seems there is a bug in how calc present the row range and how calc needs it must be defined, introducing Budget!R57:R57 seems to work fine. Please report a separated bug. With Calc or Excel A1 notation the range to define the name is right. 7)8)9) I think the same issue than in 3) or in relation. Please add a new report bug for 6) (One bug one report), and follow the other in fdo#93894, adding your comments there if you want. Closed as duplicate, please if you are not agree reopen it. *** This bug has been marked as a duplicate of bug 93894 ***
Thank you for your comments. #3/7/8/9 I did search before posting, but initially did not make the connection with fdo#93894. Thanks for finding that; I will follow there. #5 Indeed seems fixable using an absolute address. No idea why it was defined relative, probably pebkac. Will ignore this for now as I have no additional information. #6 I do not recall testing this specific issue on 5.0.x, so filed new bug regarding 5.1.1.3. See https://bugs.documentfoundation.org/show_bug.cgi?id=98990
(The FIXED status is reserved for issues closed by a commit to one of our repositories. If an issue is invalid or no longer reproducible, please use INVALID, NOTABUG or WORKSFORME.)
The original resolution was "DUPLICATE". I certainly did not intend to change the resolution with my last comment, but that seems to be what happened. Apologies.