Re: [libreoffice-users] Re: Calc: cell protection + cut & paste

El 13/04/12 22:36, miakoiv escribió:
> > -----Original Message-----
> > From: Dennis E. Hamilton [mailto:dennis.hamilton@acm.org]
> > Sent: Thursday, April 12, 2012 11:15
> > To: 'users@global.libreoffice.org'
> > Subject: RE: [libreoffice-users] Re: Calc: cell protection + cut&  paste
> >
> > 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.
>
> > 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.
>
> 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. :)
>
> Mikko
>
>
>
> --
> View this message in context: 
> http://nabble.documentfoundation.org/Calc-cell-protection-cut-paste-tp3903414p3908930.html
> Sent from the Users mailing list archive at Nabble.com.

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







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