copy down, copy and paste in calc.

[Steve_Edmonds]

This is a discussion on how this could work. Same in 3.3.2 and 3.4.0
Presently, if I put the number 9 in a cell.
Click into and copy that cell, highlight say 5 cells and paste. 9
appears in all 5 cells.
Now, click into the cell with 9, copy 9 from the formula bar, highlight
say 5 cells and paste. 9 appears only in the top cell of the 5.

When I want to copy the same formula to a range of cells without the
references indexing this is usefull, but the formula only copies to the
top cell.

Another usefull thing would be;
click into a cell with formula and drag the bottom right black square
down, the formula repeats indexing the references. (does this now).
click into a cell with formula while holding ctl (cmd) down and drag the
bottom right black square down, the formula repeats without indexing the
references, i.e. copies literally as with numbers. (does not do this now).
steve

[Jack11]

Steve Edmonds wrote:

When I want to copy the same formula to a range of cells without the
references indexing this is usefull, but the formula only copies to the
top cell.

Another usefull thing would be;
click into a cell with formula and drag the bottom right black square
down, the formula repeats indexing the references. (does this now).
click into a cell with formula while holding ctl (cmd) down and drag the
bottom right black square down, the formula repeats without indexing the
references, i.e. copies literally as with numbers. (does not do this now).

If you want to copy a formula without "indexing the reference", then
you can take a look at absolute references. It is described in the
LibreOffice wiki
(http://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative).
A short summary is:
Let's say cell C1 contains the following formula (without the quotes):
"= A1 + B1"
If you copy the formula in cell C1 to cell C2 (by either Copy & Paste,
or by dragging the corner of cell C1) you will get "= A2 + B2" in cell
C2.
If this is not what you wanted, you can change the reference in C1 to
an absolute reference.

To do this, select the cell C1 and press SHIFT+F4. The formula in
cell C1 will change to "= $A$1 + $B$1". If you copy that to cell C2,
it will stay "= $A$1 + $B$1".

If you want a more detailed explanation of absolute and relative cell
referencing, read the wiki page or ask in this mailing list for more
info.

Regards
Stephan

[Tom_Davies]

Hi
Yes, good point! You can fine-tune it by only using the $ in one part of the
cell reference so that copying in one direction does change which cells are
looked up.

For example writing this in say D4
=$A1 + C$3
means that as you copy the formula down the rows the A part doesn't change, it
stays a A, obviously. The 1 part changes. The C part also stays the same
because of the direction we are copying in but the 3 also doesn't change because
it's been fixed by the $ sign.

Copying the same formula across several columns the A will still stay fixed
because it's been fixed by the $ sign, the 1 wont change because we are staying
on the same row. The C will change tho because it hasn't been fixed.

Regards from
Tom

[Steve_Edmonds]

Thanks Stephan and Tom.
I already understand the use of the absolute referencing. I may not have expressed myself well enough to avoid confusion.
1. Enter say the digit 9 into a cell. Click into the cell, copy. Select 5 cells and paste. 9 pastes into all 5 cells.

2. Now in your email, select a word or number and copy. Select 5 cells and paste. The word only pastes into the top 1 of the 5 cells.

It would be nice if the behavior in 2 was the same as in 1.

steve

[Tom_Davies]

Hi
That is inconsistent and i hadn't noticed. It happens in LO3.3.2 on Ubuntu
10.04 too. can you post a bug-report about it?
9
Regards from
Tom

[Steve_Edmonds]

Is it a bug or an enhancement?
steve

[Tom_Davies]

________________________________
From: Steve Edmonds <steve.edmonds@ptglobal.com>
To: users@global.libreoffice.org
Sent: Mon, 13 June, 2011 18:53:36
Subject: Re: [libreoffice-users] copy down, copy and paste in calc.

On 14/06/11 12:20 AM, Tom Davies wrote:

----- Original Message ----

From: Steve Edmonds<steve.edmonds@ptglobal.com>
To: users@global.libreoffice.org
Sent: Mon, 13 June, 2011 11:31:11
Subject: Re: [libreoffice-users] copy down, copy and paste in calc.

On 13/06/11 8:59 PM, Stephan Zietsman wrote:

Steve Edmonds wrote:

When I want to copy the same formula to a range of cells without the
references indexing this is usefull, but the formula only copies to the
top cell.

Another usefull thing would be;
click into a cell with formula and drag the bottom right black square
down, the formula repeats indexing the references. (does this now).
click into a cell with formula while holding ctl (cmd) down and drag the
bottom right black square down, the formula repeats without indexing the
references, i.e. copies literally as with numbers. (does not do this now).

If you want to copy a formula without "indexing the reference", then
you can take a look at absolute references. It is described in the
LibreOffice wiki

(http://help.libreoffice.org/Calc/Addresses_and_References,_Absolute_and_Relative).
.

    A short summary is:
Let's say cell C1 contains the following formula (without the quotes):
"= A1 + B1"
If you copy the formula in cell C1 to cell C2 (by either Copy& Paste,
or by dragging the corner of cell C1) you will get "= A2 + B2" in cell
C2.
If this is not what you wanted, you can change the reference in C1 to
an absolute reference.

To do this, select the cell C1 and press SHIFT+F4. The formula in
cell C1 will change to "= $A$1 + $B$1". If you copy that to cell C2,
it will stay "= $A$1 + $B$1".

If you want a more detailed explanation of absolute and relative cell
referencing, read the wiki page or ask in this mailing list for more
info.

Thanks Stephan and Tom.
I already understand the use of the absolute referencing. I may not have
expressed myself well enough to avoid confusion.
1. Enter say the digit 9 into a cell. Click into the cell, copy. Select
5 cells and paste. 9 pastes into all 5 cells.

2. Now in your email, select a word or number and copy. Select 5 cells
and paste. The word only pastes into the top 1 of the 5 cells.

It would be nice if the behavior in 2 was the same as in 1.

steve

Hi
That is inconsistent and i hadn't noticed. It happens in LO3.3.2 on Ubuntu
10.04 too. can you post a bug-report about it?
9
Regards from
Tom

Is it a bug or an enhancement?
steve

Hi
I think either case could be argued. I think probably best to post it as a
bug-report. The link that i gave you has a lot of complicated things to do that
really are not needed for this report. It just needs a copy&paste of your email
that ended "It would be nice if the behavior in 2 was the same as in 1". Short
& sweet should be enough
Regards from
Tom

[Jack11]

Steve Edmonds wrote:

Is it a bug or an enhancement?

I notice that MS Excel also does not paste the data into all the
selected cells (when using the method described in 2. above). Excel
pops up a warning message about data on the clipboard not being the
same size as the selected range. The two options on the pop-up are
"OK" and "Cancel". Pressing "OK" causes the cell selection to change
to only the first cell, and the data is copied only into that cell
(the first one). This is more or less similar to the LibO behaviour
(except that LibO doesn't change the selection and LibO has no pop-up
message).

Based on this information, I would assume that what you are describing
would rather be an enhancement (or feature request) than a bug.
However, you can still file it as a bug, just make sure to select the
"severity" as "enhancement".

Regards
Stephan

[Tom_Davies]

Hi
It's inconsistent behaviour within the app. I owuld say that the same
bug-report needs to be posted against Excel too but i have no interest at all in
helping them out and they rarely listen to their users anyway. Ok, i could go
with Enhancement, either way is good.
Regards from
Tom

[Steve_Edmonds]

Done. https://bugs.freedesktop.org/show_bug.cgi?id=38281
steve

[Tom_Davies]

Hi
I have added a comment
Regards from
Tom