How to reproduce: Open the attachted example file. Press CTRL+F3 to verify that the names “NamedField_1”, “NamedField_2” and “NamedField_3” are properly defined, referring to $Sheet2_1.$C$3, $Sheet2_1.$C$5 and $Sheet2_1.$C$6 respectively. In Sheet2_2 select cells A4 … C4 and copy them (Using CTRL+C and CTRL+V) into the corresponding blank cells in Sheet2_1. Current behaviour: Pressing CTRL+F3 shows that “NamedField_1”, “NamedField_2” and “NamedField_3” now display an #REF!-Error. Despite this error, the value in Sheet2_1.C8 is unchanged. Note that this cell refers to the names that contain the ref error. Save and reload the spreadsheet changes the value in cell Sheet2_1.C8: It now displays an error. Expected behaviour: The names should not be affected by the insertion. I observed that copying cell ranges produces other effects too. Cells referred to different ranges after copying some unrelated cells. Unfortunately I was not yet able construct a situation that reliably demonstrates this behaviour. As the correct reference information is lost, this error involves loss of data and that's why I classified it as major error. What makes the situation worse is that this loss of information may go unnoticed: Cell Sheet2_1.C8 remains unchanged despite the fact the the reference information became invalid. There are a couple of bugs that seem somehow related to this one: Bug 81330, bug 67581, bug 81295 and 81294.
Created attachment 102929 [details] Example file to show the effect.
Forgot to mention: Observed with Libre Office Version: 4.2.5.2 and Windows 7
Thank you for reporting this issue! I have been able to confirm the issue on: Version: 4.3.0.2 rc Platform :Ubuntu 14.04 DE: GNOME3 + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + As I've been able to confirm this problem I am marking as: New (confirmed) Major - essentially loss of data, no way to recover this and you wouldn't know about the reference error before saving, closing, and then reopening High - default seems appropriate @Albrecht - is this a regression? If so please add "regression" to keyword field, bibisectRequest to whiteboard field and leave a comment letting us know in what version it works. + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage and join us on freenode at #libreoffice-qa There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Reproduced with LO 4.2.6.1, 4.2.4.2, 4.2.0.0.beta1 - Ubuntu 12.04 x86 Not reproduced with LO 4.1.6.2
@Joel I think ign_christian already provided the information you needed. I had no way to check if this ever worked as I have only one installation of Libre Office and my bug reports resulted from my first trial to combine the contents of two spreadsheets. To put these problems in context here is the story of the user experience: I have a couple of spreadsheets containing diagrams together with the corresponding data. They are essentially modified copies of a common ancestor, thus containing roughly the same set of names. The purpose of using names is to avoid mistakes when adapting the copies to different data. I wanted to sum up the contents of two of these spreadsheets. I ruled out the option to create an additional spreadsheet for the following two reasons: a) I fear that Calc can only use absolute references to other files. Therefore moving or renaming the containing directory would break references to the other spreadsheets. b) I wanted to reduce the number of files. Therefore I wanted to create a copy of one file, add the sheets contained in the other file, create an additional summary sheet and delete the original files. I thought this would be a quite common task and therefore did not prepare the files for the merge assuming that a reasonable spreadsheet program would detect the problems related to this operation and do something useful about it. Obviously there are name collisions which could be resolved for example by renaming. There are also ambiguities when names are copied: Should they point to the original file or to the correspondig location in the targed spreadsheet? What should happen, when the specified range does not (yet - assuming you copy the sheets one by one) exist? So I went ahead and what followed was a nightmare. When things did not run as smoothly as expected the first idea was to look at the names: Select a cell in order to see its formula in the input line, press CTRL+F3 to get the names dialog, arrange dialog size and colums so to be able to see the relevant information. Now a look at the input line to check it against the names dialog: Uups - the content of the input line is gone (see bug 81291). Close the dialog and the input line is there again, but now I was not able to see the name information. Trying to remember the necessary information and opening the names dialog again. Now I have to rearrange the contents of the name dialog again, otherwise I cannot see what I want to check. Done - oh, now I an not sure if I remembered correctly what I wanted to check ... I think you get the picture - gives bug 81293. After having developed strategies to cope with this behaviour, it took me hours of experimenting until I was able to distinguish between some of the strange effects I observed and to create a few examples demonstrating them: A data loss, where actually no data were lost - bug 81294, fixed already. I could create an example that - in my opinion - demonstrated that something was wrong with name scope handling (bug 81295) as switching the scope of a name and changing it back to the same value as before resulted in permanent changes in the contents of the spreadsheet. In the meantime I learned that what I considered a data loss seems to be the intended default behaviour. No problem, as everybody (now including me too) knows that there is a switch that allows you to change this behaviour. Due to my ignorance of this fact the idea to repeat the name used for a cell range as a header for that range caused me quite a few headaches. And there is the situation where all things look good, but it is just because you do not notice the loss of data immedately - which is bug 81435 (this one). In my opinion this kind of error is especially nasty as you may detect the problem only a long time after the actual loss of data and therefore you cannot tell when and why it happened. This ruins the trust in the program. I also encountered some errors that are already known for quite some time (e.g. bug 43003, bug 67581, bug 74014, bug 74756 and bug 81330), and additional problems I did not investigate further or create bug reports for, e.g. how do I make sure that my spreadsheed does not contain references to others spreadsheets? How do I find the cells containing the external references (including those contained in diagrams)? All these these things occured while I tried to achieve what I think is a common and basic task - summing up the contents of two spreadsheets. And that's only Calc, there is Writer loosing references to headlines and Base truncating MS Access Memo Fields to about 255 bytes ... (No bug reports created for these, too much work to find out the exact situations that reproduce these problems). Libre Office may be quite expensive for companies that have to pay for working time.
Just FWIW, keeping your comment to a bare minimum by not dumping all your emotions, conjectures, speculations and by presenting only factual observations would be very much appreciated. To be honest, a bug report with comments as large as Comment #5 only acts as a developer repellant. A lot of bugzilla users do this, and it makes our job as a developer 100 times harder.
Created attachment 104785 [details] bibisect log Bisected, but lots of skip,because in some versions range didn't show.
Created attachment 109566 [details] Test Spreadsheet to display issue. Highlight the cell containing "Edit" hit Ctrl-X, move the curser anywhere and hit "Return/Enter".
Hello: In reviewing this thread it seems I am having a similar behavior. I'm running LibreOffice 4.2.7.2 on Ubuntu 14.04. When I have a cell reference in one sheet to another, if I attempt to edit anything using Ctrl-X then Return the reference to the other sheet is deleted. In my attached example I have a number in Sheet1 that I reference in Sheet2 as "=Sheet1.B5" when I attempt to edit "Edit" using Ctrl-X then Return the reference in Sheet2 changes to simply "=B5". What's worse is the display in the field in Sheet2 doesn't change, leaving no clue to the error unless I hit Ctrl-Shift-F9 to force a recalculation. Charles
(In reply to Charles Brescia from comment #9) Hello Charles, I think the bug you reported is related to this one, but I could not reproduce it with LibreOffice Version: 4.3.2.2, Build ID: edfb5295ba211bd31ad47d0bad0118690f76407d under Windows 7. Maybe it is fixed already. My original problem given in attachment 102929 [details] is still there, however. Your report inspired me to try my example using CTRL+X and CTRL+V instead of CTRL+C and CTRL+V, i.e. moving the content instead of copying it. This produced the following results: a) Moving Content using CTRL+X and CTRL+V: References remain intact. b) Copy using CTRL+X and CTRL+V at the original place (to restore the content) and then CTRL+V at the target location: References get damaged in the same way as if the equivalent CTRL+C and CTRL+V had been used. c) Copy using a move CTRL+X and CTRL+V at the target location first and then an additional CTRL+V at original place to restore the content: References remain intact. There is an additional strange effect: Move the cells in Sheet2_2.A3:C7 to Sheet2_1.A1:C14 using CTRL+X and CTRL+V. Then cell C14 shows a zero value. Applying CTRL+Shift+F9 does not change this. Use CTRL+C and CTRL+V to copy the contents of this cell into some other empty place in the same sheet. The copy shows the correct value 21. You produced two cells containing exactly the same content but displaying different values. Now copy cells A1:C14 to their original place again using CTRL+C and CTRL+V. This time the sum filed show again its correct value. Change a value in one of the fields C3, C4 or C5. The sum value in C7 will not change. You can see the reason by pressing CTRL+F3: The variables point to the cells in Sheet2_1 instead of Sheet2_2. If you switch back to Sheet2_1, it is interesting to see that cell C14 now shows the value that should appear in the sum cell of Sheet2_2. You still have two cells containing exactly the same content but showing different values. Edit cell C14, just add a blank at the end of the content line and press enter. Now this cell and its copy show the same value. Albrecht
I think, my case is related somehow to the same problem: example - there are few sheets in workbook, one of them contains data, the others - calculations involving data from the first. If I copy the sheet to the new (actually duplicating one of calculation sheets), the resulting sheet has damaged references to the data sheet (for example: COUNTIF(#REF!.$D$150:#REF!.$K$250;B36) all the references to data sheet being replaced by #REF!). LO 4.3.3.2/Slackware64 14.1 Janis
by the way - I can confirm original bug on LO 4.3.3.2/Slackware64 14.1 Janis
The originally reported bug from comment 0 appears to have been fixed in the below commit, which is in 4.5 master and 4.4 but not 4.3. Adding Cc: to palenik.mihaly@gmail.com. Could this be appropriate as a backport for 4.3? commit f92ab4da51647a4353038b1c56b70db3672c49cf Author: Palenik Mihály <palenik.mihaly@gmail.com> Date: Wed Oct 22 19:01:05 2014 +0200 Improve SvSimpleTable class It is possible to order columns. This is set in Expert Configuration dialog. The header's itembits weren't set correctly therefore mouse click handler didn't do anything. The comparsion was slow on big table. Conflicts: include/svtools/treelist.hxx svtools/source/contnr/simptabl.cxx Change-Id: I7e1301d40433ef45b3d0a3fb300909345ede9d4d Reviewed-on: https://gerrit.libreoffice.org/12070 Reviewed-by: Andras Timar <andras.timar@collabora.com> Tested-by: Andras Timar <andras.timar@collabora.com>
(In reply to Albrecht Müller from comment #0) > How to reproduce: > Open the attachted example file. Press CTRL+F3 to verify that the names > “NamedField_1”, “NamedField_2” and “NamedField_3” are properly defined, > referring to $Sheet2_1.$C$3, $Sheet2_1.$C$5 and $Sheet2_1.$C$6 respectively. > In Sheet2_2 select cells A4 … C4 and copy them (Using CTRL+C and CTRL+V) > into the corresponding blank cells in Sheet2_1. > > Current behaviour: > Pressing CTRL+F3 shows that “NamedField_1”, “NamedField_2” and > “NamedField_3” now display an #REF!-Error. Despite this error, the value in > Sheet2_1.C8 is unchanged. Note that this cell refers to the names that > contain the ref error. TESTING with Ubuntu 14.04: 4.3.5.2 - #REF error confirmed 4.3.6.1 - NOREPRO: There's no #REF!-Error This appears to be fixed on the 4.3 branch, so Status -> RESOLVED WORKSFORME Whiteboard -> (remove) backportRequest:4.3
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]
Created attachment 157283 [details] a_map/sheet_with_name_reference_fails hi, while reviewing this thread i saw something left from the problem, i produced a simplified testsheet to recheck, see attachement, the issue is 'inspired' by the fail from comment #10: "There is an additional strange effect: Move the cells in Sheet2_2.A3:C7 to Sheet2_1.A1:C14 using CTRL+X and CTRL+V. Then cell C14 shows a zero value. ... " problem: the name-reference is changed on move, but not rechanged on undo. it's two issues, one for moving into one sheet, another for moving to another sheet, the steps to reproduce the issue are noted in the sheet, copied here: A) name references messed up by moving of content into the sheet and undo steps to reproduce 1. check name reference 'testfield_1' to sheet1.B2 (hit ctrl-F3), scope of reference: sheet1 2. move sheet1.B2:sheet1.B3 to sheet1.C2:sheet1.C3 (ctrl-X, ctrl-V) 3. check new name reference 'testfield_1' with target C2 and scope sheet1, in the lower part of the box 'ctrl-F3' the scope is announced as global! 4. undo move – ctrl-Z – ctrl-Z 5. check result in sheet1.B3: '=#NAME?' despite 'testfield_1' still defined, but pointing to $C$2 imho the change by the move should be undone by undo (if you undo the move by another move with exchanged source-target references are handeled correctly) B) name references messed up by moving of content to another sheet and undo steps to reproduce 1. check name reference 'testfield_1' to sheet1.B2 (hit ctrl-F3), scope of reference: sheet1 2. move sheet1.B2:sheet1.B3 to sheet2.B2:sheet2.B3 (ctrl-X, ctrl-V) 3. check new name reference 'testfield_1' with scope sheet2, 3a. check old reference with scope sheet1 remains, but now points to new location (on sheet2) 4. check result in sheet2.B3: '=#NAME?' 5. undo move – ctrl-Z – ctrl-Z 6. check sheet1.B3 result now '0', it still references to testfield_1 on sheet2 test by input number to sheet2.B2 7. remove name reference testfield_1 from sheet2 8. observe result in sheet1.B3 unaffected, 9. change value in sheet2.B2 10. observe sheet1.B3 still calculating with the removed reference (if you undo the move by another move with exchanged source-target references are NOT handeled correctly) would be nice if someone rechecks, and if confirmed finds somebody to correct the fault, if - in this case - an experienced forum member would prefer a new case instead of poking around in old stuff ... feel free to re-close this and open a new one. reg. b.
Set status back to New as this was the last status before it was closed.
rechecked today with below ver. - both issues from c#16 changed a little, but still buggy ... Version: 7.1.0.0.alpha0+ (x64) Build ID: 586f6abee92af3cdabdce034b607b9a046ed3946 CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win Locale: de-DE (de_DE); UI: en-US Calc: CL
Created spin-off bug 148072 with comment 16, closing this here as fixed.