Calc: cut/paste removes and renames protected field references - is this a bug?

Brian_Jamison · October 13, 2011, 3:50am

Hello and first let me say how awesome LibreOffice is compared to ooo.

I've come across some strange behavior, whether or not I've found a bug I'd appreciate some advice for a workaround.

The behavior is this -- in calc, if you cut the data in a cell and paste it into another cell, field references in a protected sheet are changed.

I'd expect a protected sheet to stay fixed and not be subject to the effects of a cut on another sheet. I've tried relative and absolute field references (Sheet1.A1, $Sheet1.$A$1 and $Sheet1.$A1) with the same result, perhaps I'm not doing it right.

Steps to reproduce:
On Sheet1 put data in fields A1, A2
On Sheet2 reference A1 and A2 in cells (e.g. =$Sheet1.$A$1, =$Sheet1.$A$2)
Protect Sheet2
Cut the data in cell A1
Paste the data into A2
Cut the data in cell A2
Paste the data into A1

Both field references in Sheet2 will now reference Sheet1.A2.

Any thoughts would be appreciated.

-Brian

Jack11 · October 13, 2011, 7:57am

Brian Jamison wrote:

Steps to reproduce:
On Sheet1 put data in fields A1, A2
On Sheet2 reference A1 and A2 in cells (e.g. =$Sheet1.$A$1, =$Sheet1.$A$2)
Protect Sheet2
Cut the data in cell A1
Paste the data into A2
Cut the data in cell A2
Paste the data into A1

Both field references in Sheet2 will now reference Sheet1.A2.

I think you meant to say that both fields reference Sheet1.A1 (and not
Sheet1.A2). If so, I can verify the behaviour as described (just
tested). I can't comment on whether it's a bug or intended behaviour
though, as I really don't know.

Regards
Stephan

Andreas_Sager · October 13, 2011, 10:23am

Am 13.10.2011 05:50, Brian Jamison wrote:

Steps to reproduce:
On Sheet1 put data in fields A1, A2
On Sheet2 reference A1 and A2 in cells (e.g. =$Sheet1.$A$1, =$Sheet1.$A$2)
Protect Sheet2
Cut the data in cell A1
Paste the data into A2
Cut the data in cell A2
Paste the data into A1

This is expected and wanted behaviour in all spreadsheet applications.

Brian_Jamison · October 13, 2011, 3:48pm

Andreas Säger-2 wrote:

This is expected and wanted behaviour in all spreadsheet applications.

That's odd. How can I truly protect the field references on a protected
sheet from change?

Andreas_Sager · October 13, 2011, 4:30pm

Am 13.10.2011 17:47, Brian J. wrote:

Andreas Säger-2 wrote:

This is expected and wanted behaviour in all spreadsheet applications.

That's odd. How can I truly protect the field references on a protected
sheet from change?

--

You mean: How can I refer to one particular cell address?

=OFFSET($SheetX.$A$1;2;3;4;5)
refers to SheetX.A1, 2 rows down, 3 columns to the right, resized to 4 rows and 5 columns. That is SheetX.D3:H6 unless you move SheetX.A1.

=INDEX($SheetX.$A$1:$X$999;3;4) refers to row #3, column 4 within SheetX.A1:X999 unless you move the entire range SheetX.A1:X999.

Brian_Jamison · October 14, 2011, 12:50am

Andreas Säger-2 wrote:

You mean: How can I refer to one particular cell address?

=OFFSET($SheetX.$A$1;2;3;4;5)

No, I mean how can I refer to a particular cell address no matter what a
user does to that cell. Moving, cutting, pasting, overwriting, anything. I
always want to point to, say Sheet1.A1.

If you think the use case would be helpful I'd be happy to elaborate.

Steve_Edmonds · October 14, 2011, 1:05am

Hi.
What about trying
=INDIRECT("sheet1.a1")

If my understanding of your requirement is correct, this seems to work.
steve

Tom_Davies · October 14, 2011, 1:33am

Hi
Can you name a cell? In Excel you could define a name for a cell by just typing it in on the formula bar just before the f(x)Sigma bit. Normally the drop-down there contains the cell's address, such as A!, but you can just type something else into there to rename it. Try calling it "fred" and then in cell B6 or somewhere try writing
=fred
Now you can move A1 around by drag&drop or cut&paste and B6 will keep referrign to that same cell even tho it's not A1 anymore.
Regards from
Tom

Andreas_Sager · October 14, 2011, 1:46am

Am 14.10.2011 03:32, Tom Davies wrote:

Hi
Can you name a cell? In Excel you could define a name for a cell by just typing it in on the formula bar just before the f(x)Sigma bit. Normally the drop-down there contains the cell's address, such as A!, but you can just type something else into there to rename it. Try calling it "fred" and then in cell B6 or somewhere try writing
=fred
Now you can move A1 around by drag&drop or cut&paste and B6 will keep referrign to that same cell even tho it's not A1 anymore.
Regards from
Tom

Tom, you never test your own suggestions.
*All* spreadsheet applications keep track of *all* moving references, named or unnamed.
By now we have 3 formulas to keep frozen references.

Brian_Jamison · October 14, 2011, 4:01pm

Tom wrote:

=INDIRECT("sheet1.a1")

You sir, are a genius. Thank you, that did exactly what I needed.

Tanstaafl · October 14, 2011, 4:08pm

That wasn't Tom, it was Steve Edmonds...

cornouws · October 14, 2011, 4:09pm

Hi Brain,

Brian J. wrote (14-10-11 18:01)

Tom wrote:
[..]
You sir, are a genius. Thank you, that did exactly what I needed.

The part you quoted, was from

I am not sure if Tom deserves it to be mentioned a genius - he seems not to able to manage his reply-to-settings in such a way, that it does not confuse you or others. Alas.

Kind regards,

V_Stuart_Foote · October 14, 2011, 5:22pm

Cor,

Ouch! Too harsh, take it down a notch...

Stuart

Tom_Davies · October 15, 2011, 4:45am

Hi
Congrats on fixing the problem Thanks for the flowers but it was Steve
Edmonds that gave the answer that helped you there so i'll pass the flowers
on to him In Nabble it is possible to check a star by a good answer so i
marked Steve's post like that.

One of the dangers of snipping is that personal biases or misunderstandings
can get exaggerated but on the plus side that then often leads to people
correcting those misunderstandings. Often lots of people are quick to
criticise even if they weren't willing to make their own suggestion to try
to help.

Naming cells seems to deal with the problem for me but i think i must have
missed the point. I have dragged named cells all over the place and moved
them to other worksheets, over-written the values in them and moved the cell
containing the formula similarly and the value produced is calculated from
the correct cells where-ever they are. I guess i missed the first post in
the thread and it got snipped from all the replies.

Regards from
Tom