Although that is surprising behavior, it is what happens when a cell used by a formula is moved by cut and paste. The formula can't be edited, but it is still responsive to relocation of cells it depends on.
It is a little surprising in the case of a protected-formula cell. The way to deal with it in a protected sheet is to use absolute references in the protected formula. (Unfortunately, disallowing selection of unprotected cells prevents too much.) It looks like a bigger issue might be whether it is possible to prevent editing operations (cut especially but also row-column insertions and deletions) in a sheet with protections turned on. All while still allowing data entry into unprotected cells. - Dennis PS: I just tried this in Excel 2010. The same thing happens to the C1 formula but pasting the cut of A1 into A2 causes the C2 formula to show a #REF! error. But if I cut the two-cell selection of A1:A2 and paste it into A2 (so A2:A3 get the result), there's no complaint and both the C1 and C2 formulas have their references to column A adjusted. (Excel 2010 offers an incredible number of controls on what can be done with the protected sheet though, including control of what can be selected.) -----Original Message----- From: MiguelAngel [mailto:mari...@miguelangel.mobi] Sent: Thursday, April 12, 2012 09:01 To: users@global.libreoffice.org Subject: Re: [libreoffice-users] Re: Calc: cell protection + cut & paste El 12/04/12 6:28, miakoiv escribió: >> miakoiv wrote (11-04-12 20:32) >> >>> If a protected cell reads data from non protected cell and the user >>> decides >>> to cut + paste the information, the orginal formula in the protected >>> cell >>> will be modified. >> >> I don't get this. >> When data, that is used in a formula is changed, removed, the result of >> the formula will change, not the formula. > > Example: > > A1: 1 B1: 2 C1: =A1+B1 (result: 3) > A2: 2 B2: 1 C2: =A2+B2 (result: 3) > > Now you protect cells C1 and C2. Now this is an important spreadsheet to > customer and their employees fill the data to cells A1, A2, B1, B2. And it's > of course important that the calculations are correct. > > Now the customer enters data to A1 which was supposed to go to A2. He/She > cuts the data from A1 and pastes the data to cell A2. > > This is the result: > (he/she wanted to enter 1 to A2, but entered it to A1 and then cut + pasted > it to A2. Then he/she entered number 2 to A1) > > A1: 2 B1: 2 C1: =A2+B1 (result: 3) > A2: 1 B2: 1 C2: =A2+B2 (result: 2) > > So the formula in C1 is now A2+B1 which is different than A1+B1 so it > changed. And since the cell is protected, you can't change it back to > correct one unless you cut + paste again. But if the formulas are also > hidden, then it's totally impossible to fix it anymore. > > I've heard some comments like "This is expected and wanted behaviour in all > spreadsheet applications.", but then I would like to ask what's the point of > protection when you can't protect the formulas from even the simpliest user > errors? I know that this happens in Excel too, but there you are able to > prevent user from using cut+paste (and if googled, you'll notice that people > use this quite much just for this reeason). > > If this is a wanted behaviour, it should atleast give a warning to the user. > "Are you sure you want to cut + paste data? It will modify protected > formulas and you won't be able to fix those anymore and you will get wrong > results afterwards? So are you sure?" :-) > > Miakoiv > > > > > > > > -- > View this message in context: > http://nabble.documentfoundation.org/Calc-cell-protection-cut-paste-tp3903414p3904507.html > Sent from the Users mailing list archive at Nabble.com. A workaround is INDIRECT() function to avoid the cut/paste: C1: =INDIRECT("A2")+INDIRECT("B1") This need edit C1 to change the reference to cells, because as you can see the references are texts and don't change with cut/paste A2 and B1. Miguel Ángel. -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- For unsubscribe instructions e-mail to: users+h...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
