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:mariosv@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+help@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+help@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