[Solved] Behavior of formulas when moving rows

[lanyi]

Hello,
I am having a very hard time moving entire rows in the spreadsheet because of what happens to the formulas.
I use some very simple formulas in the spreadshee, for example:
The formula in cell H300 is: =C300*A28
In C300 there is an integer (the number of items for sale)
In A28 there is another integer (the price, in this case, 28 cents)
So if the value in C300 is 10, H300 will be 280.
So far so good.
Now if I move this entire row 5 rows down, to row 305, the formula I expect to get in H305 (what I get in Excel and Google sheets) is:
=C305*A28 so the value remains 280
In fact, what I get is =C305*A33, so now the value is 330.
It gets worse.
If now I move the row up 10 rows, to row 295, the formula in cell H295 is replaced with the integer 330.
This happens consistently, so I'm sure I must be doing something wrong. It cannot be that this is the only way the system would work.
Is there any way for me to indicate that in the formula =C300*A28 I want the C300 part to be updated to the current row wherever I happen to move it but the A28 to remain the same? (In Excel and Google sheets this is the default behavior).
Thank you so much for your help.

[FJCC]

If you want the formula to multiply the value in column C of the current row by A28 and continue using A28 if the formula is moved, write

Code: Select all

=C300 * $A$28

$A$28 is an absolute reference and will not change. Writing C300 in a formula in H300 actually means "the cell in this row five columns to the left."

[lanyi]

Thank you very much. Now I remember that this is how we used to write it long ago, until it has become default behavior in most spreadsheets.
But this won't solve my other problem, of replacing formulas with their values whenever I move up a row. Or will it?

[FJCC]

Yes, you should be able to move the formula up. I've never seen the behavior of replacing the formula with its value if you move a cell up. Can you upload this document? Or does it happen with all documents?

[FJCC]

By the way, when you say you move the row up, how are you doing that? I assumed it was a Cut and Paste operation.

[robleyd]

See also [Tutorial] Absolute, relative and mixed references

[lanyi]

Yes, I Cut and Paste special. But actually, I tried it again with the $ definition and the pasting works fine in both directions. I will also read the tutorial on the different types of references. Thank you very much for your help.

[lanyi]

OK, so I looked into it more carefully:
I replaced the relative references in the formulas with absolute ones: $A$33.
- When I drag up or down (rather inconvenient if one needs to drag across dozens or more rows), the formulas are preserved intact. (Dragging is especially inconvenient when one needs to drag a whole bunch of rows together.)
- When I cut/paste or copy/paste, the formulas are replaced with their numeric values.
This cannot be the nominal behavior...

[FJCC]

No, that isn't normal if I understand what you are doing. Can you upload a document that shows this behavior? To upload a file, click Post Reply and look for the Attachments tab just below the box where you type a response.

[lanyi]

Thank you so much for your help.
I'm uploading here a simplified version of the file, but if you go to the bottom and copy/paste any row you will see that the formula in the D column is converted into a value.
I greatly appreciate your help. I love this spreadsheet and I would hate to have to give up on it.

[FJCC]

I copy the range B470:F470 and pasted it in B462:F462. The formula in D470 was =C470 * A26. In the new location, the formula became =C462 * A18, just as expected. If you do that, do you get a different result?

[lanyi]

Yes, I get exactly the same thing. But this is not how I need to use the spreadsheet.
There happened to be an empty row at 462. But when I need to move a record (say, I job has been invoiced or paid) to the area where invoiced or paid records are, I need to copy the entire row and paste-special it in, pushing down all the records below it. The formulas are not preserved when I select the entire row.
Moreover, sometimes I need to move 10 records at a time. To do it the way you were able to, I would need to count the number of records, go to the destination, open the same number of empty rows, and then paste in the records. It is not practicable. I could do it by dragging (that actually works) but it also overwrites the data in the existing rows and dragging across dozens of rows is again not feasible.
In Google sheets the copy/paste didn't work either but at least I could drag and the formulas were preserved and new rows opened for the rows being dropped in.
I can't believe that this is the only way the application works.

[FJCC]

I changed the formula in D470 to be =C470*$A$26. I selected the entire row and copied it. I selected cell A454 and pressed CTRL + Shift + V to bring up the Paste Special dialog. I selected to paste Text, Numbers, Date & Time, and Formulas. I also set Shift Cells to Down. After clicking OK, the formula in D454 is now =C454*$A$26, which is what I would expect.

[lanyi]

Wow!!! Thank you, thank you!
1. I didn't think of checking Formulas. For some reason, I looked only at the Shift Cells Down area of the dialog.
2. And I would never have thought of selecting the cell in column A rather than the entire row.
This totally solves my problem.
I'm really grateful for your help.

[Alex1]

If you want to move one or more rows, drag them while holding down the Alt key.

[MrProgrammer]

I am having a very hard time moving entire rows in the spreadsheet because of what happens to the formulas.

I move rows up or down with two macros, CutRows and InsertCBRows (first tutorial). I've assigned keyboard shortcuts to both macros. They will adjust formula =C300*$A$28 to =C305*$A$28 when you move row 300 to row 305.
[Tutorial] Favorite Recorded Calc Macros
[Tutorial] Formula Adjustments during Copy and Move
[Tutorial] Ten concepts that every Calc user should know