I spoke too soon. Absolute references don't solve this problem, because even those are updated when moves happen, such as the cut and paste in the demonstration example. There actually needs to be a way to block cutting and other operations that relocate cells if formulas are to be protected from movement of their operands.
-----Original Message----- From: Dennis E. Hamilton [mailto:[email protected]] Sent: Thursday, April 12, 2012 11:15 To: '[email protected]' Subject: RE: [libreoffice-users] Re: Calc: cell protection + cut & paste 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:[email protected]] Sent: Thursday, April 12, 2012 09:01 To: [email protected] 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: [email protected] 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: [email protected] 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
