May 12 2019 06:01 AM - edited May 12 2019 06:41 AM
I have the folowing formula for B2 =B1+A2
My rows go until for example 99, with the same formula on B column. But I sometimes Copy/Paste rows - for example row 2 to row 98. In this case, my formula should be =B97+A98. Instead, it remains B98 =B1+A98
How can I define the referece cell as being always the above one, no matter where I move the row/position ?
May 12 2019 07:15 AM
May 12 2019 07:35 AM
Hi,
The cell references in the formula need to be relative which is the default.
The formula you have in cell B2 is completely relative:
=B1+A2
To copy it all the way down, use the Fill Handle.
You will find the Fill Handle in the right-hand corner of the cell.
If you want to lock a cell in a formula so that it doesn't move when you copy the formula down, you need the use the absolute reference.
Try this example:
=B1+$A$2
When you copy this formula down, the cell A2 will not change.
Hope that help
May 12 2019 08:43 AM
Thank you for taking your time to answer me.
I copied in B2 your formula: =SUM(CellAbove,A2) but it returns ERROR. Thou I did not do anything with
"Select B2. Define CellAbove with this formula:=!B1" part. What do you mean by: -Define CellAbove with this formula:=!B1?
May 12 2019 08:49 AM
SolutionMay 12 2019 09:11 AM
Now, this is realy working, thank you!
I also tried to do the same thing in Google SpreadSheets, but it is not working.
May 12 2019 10:28 AM
May 12 2019 08:49 AM
Solution