Formula incorrect after paste special

English
#1

In this message, typing an asterisk seems to change the text to italics. I cannot figure out how to change this, so I have inserted the word ‘star’ when I want to use an asterisk. Advice on how to avoid this will be appreciated.

I have a spreadsheet where C4 contains this formula -

=ROUND(D3 * 0.085/365 * (A4-A3),2)

I want to copy Row 4 to Row 5. I select Row 4 and press Ctrl+C.

If I use Paste, the formula in C5 looks like this -

=ROUND(D4 * 0.085/365 * (A5-A4),2)

This is what I want.

If I use Paste Special, using ‘Paste All’ and ‘Shift Down’, the formula in C5 looks like this -

=ROUND(D3 * 0.085/365 * (A5-A3),2)

As you can see, references to Row 4 are changed to Row 5, but references to Row 3 are not changed to Row 4.

Is this the expected behaviour?

Why vlookup return error when i moved reference data?
#3

Use the Preformatted text icon </> to format required text. I’ve taken the liberty of adjusting your question accordingly.

See Discourse Guide: Code Formatting for a lot more options.

1 Like
#15

Paste Special with Shift Cells Down is the same that to insert new rows and then Paste Special with Don’t shift. To see that, you can Undo twice.

When you used Paste, first you selected row 5, otherwise it would never be pasted there.
So, when you want to Paste Special, you need to select row 5, and not to remain in row 4. This is the expected behavior.
I you remain in row 4, row 4 goes down as row 5, and a new row 4 is pasted. But A3 cell was not moved when a new row was inserted below it, so there is no need to change the reference to this cell.

Tested in version 7.0.6.2 (x64), on Windows 10.0.

#2

If you want to “fix” cell A3 in a copy-paste example, then you should use $ to show it as an absolute reference - $A$3

Discourse markdown uses the star symbol to indicate that the text that follows is in italics. Two stars will use bold text. You switch off the feature by repeating the operand at the end of the text you wish to mark as either in italics or bold.

#5

Thanks, Graham, but that is the opposite of what I want.

Paste Special is treating A3 as if it is an absolute reference. I want it to be treated as a relative reference.

Paste does treat it as a relative reference.

#6

Then I’m afraid your question is unclear.

#7

Original -

C4: =ROUND(D3 * 0.085/365 * (A4-A3),2)

After Paste -

C4: =ROUND(D3 * 0.085/365 * (A4-A3),2)
C5: =ROUND(D4 * 0.085/365 * (A5-A4),2)

After Paste Special -

C4: =ROUND(D3 * 0.085/365 * (A4-A3),2)
C5: =ROUND(D3 * 0.085/365 * (A5-A3),2)

The resulting formula in C5 differs depending on whether I use Paste or Paste Special. I would like to know if this is intentional or unexpected. If intentional, I would like to know the underlying reasoning.

#8

Perhaps this documentation of the Paste Special function may help you understand a little more.

My preference for copying formulas down, or across, is to drag the cell(s) needed using the drag handle at the bottom right of the selected cell or cell range.

#9

The “normal” value for “Shift Cells” is “Don’t shift” (corresponds to Excel method of the same name). Other values should only be used after careful study.

#10

Thanks, Vladimir.

In Excel, I can select one or more rows, right click, and select ‘Copy’. If I then select another row and right click, there is an option ‘Insert copied cells’.

I am looking for the same functionality in Calc, if it exists.

#11

The same in Calc, but right mouse click on row label (left).

#12

I tried that.

I don’t see an option ‘Insert copied cells’. I just see ‘Paste’ and ‘Paste Special’.

If I select ‘Paste’, it just overwrites the rows selected, it does not insert them.

If I select ‘Paste Special’ and ‘Don’t Shift’, the same happens.

If I select ‘Paste Special’ and ‘Shift Down’, it does insert the rows, but then I am back to the subject of this post - not all formulas are adjusted correctly.

#14

I saw a message here from Leroy, but it seems to have disappeared.

However, I tried his suggestion, and it worked!

I was not selecting the correct row before pasting. Now it it is doing what I want.

Thanks very much.

#16

Yes, I was making more tests and editing it.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.