Calc: cell protection + cut & paste

users
#1

Hello,

I made really nice project for my customer with Calc because we didn't want
to use Excel, but now we are in trouble, because in Calc you can't protect
the formulas in cells from cust + paste modifications.

Cells are protected and documentation says that when the cells are protected
you are not able to modify the formula. But that's unfortunately not true.

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. And that's a very bad behaviour in my opinion. It should
prevent cut + paste, because to allowing to do the cut + paste requires
modification of the formula and that should not be allowed since the cell is
protected.

It's totally impossible to tell customer to undo exact the same cut + paste
that he/she just did. Especially they propably wont notice the mistake until
it's too late.

Does anyone have any usable solution for us? I'm are getting desperate here
because I need to fix these errors every week.

Is this a known bug or is this designed behaviour? I'm really keen to know
because if this is a real feature, then we have no choice but to use
something else here.

Yours,

Mikko Koivisto

#2

Hi Miakoiv,

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.

Pls explain more in detail what the situation is you talk about.
An example of the cells involved maybe?

Thanks,

#3

Am 11.04.2012 23:59, Andreas Säger wrote:

Am 11.04.2012 22:58, Cor Nouws wrote:

Pls explain more in detail what the situation is you talk about.
An example of the cells involved maybe?

Thanks,

Cell protection can be overridden by pasting/dragging arbitrary other
spreadsheet cells over the protected ones.
The underlying problem is a misconception of spreadsheets (hammer/nail
problem).

Sorry, the above statement is plain wrong. I mixed protection and validation. You can paste arbitrary cells over validated cells which is also an unexpected effect when you believe in security on document level.

#4

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?" :slight_smile:

Miakoiv

#5

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.

#6

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.)

#7

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.

#8

Yes, absolute references don't solve it.
And I really do agree that there should be a way to control how these
editing operations affect protected cells.

One of the core fuctions of a speadsheet program (in my opinion) in that
somebody skills can make a calculation sheet and other people can use it to
fill needed data and do the calculations and get the needed data.

In excel you build your own UI to enter data and with VB you can do a lot of
things. But in my opinion that's just overkill for most of the cases.

Somebody said that it's an expected (and maybe even wanted?) behaviour, but
I just can't understand it.

I wish I could do the coding needed, but unfortunately I'm not skilled
enough.

I think that with a small improvement LibreOffice could be a lot better tool
without too complicated things (like Excels UI + VB)

I hope somebody, who could do something about this, finds this interesting
and needed. :slight_smile:

Mikko

#9

A workaround is INDIRECT() function to avoid that cut/paste change the references:

C1: =INDIRECT("A2")+INDIRECT("B1")

other options can be used like OFFSET() or INDEX(), and I don't know how, maybe is easy to disable the cut option.

To avoid delete/insert rows/columns use an array formula:

An array formula in A1 like = {IV1:IV100} doesn't let delete/insert rows in the range of the array formula, the same for columns with a column array.

Miguel Ángel

#10

Am 13.04.2012 22:36, miakoiv wrote:

Yes, absolute references don't solve it.
And I really do agree that there should be a way to control how these
editing operations affect protected cells.

One of the core fuctions of a speadsheet program (in my opinion) in that
somebody skills can make a calculation sheet and other people can use it to
fill needed data and do the calculations and get the needed data.

There is no need to complicate things just because the simple rules of a spreadsheet don't fit your current requirements.
Absolute/relative referencing has nothing to do with the protection state. The two features are completely independent from each other. There must not be any special meaning of absolute references for protected cells.
As a matter of course, the spreadsheet program keeps track of the references when you move around referenced cells. There are ways to work-around this behaviour (INDIRECT, INDEX, OFFSET).
What you may have in mind may be more like a database form. LibreOffice supports databases and database forms.