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