Forum Discussion
Define the reference cell as being always the above one, even when I copy/paste the row
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 ?
- Select B2. While B2 is selected, press Ctrl+Alt+F3, enter CellAbove as the name, in the refers to box, enter this formula:
=!B1
Click OK.
6 Replies
Hi,
The cell references in the formula need to be https://support.office.com/en-ie/article/switch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9 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 https://www.youtube.com/watch?v=kgGF136-kIg.
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
- Select B2. Define CellAbove with this formula:
=!B1
Enter this formula in B2, copied down rows:
=SUM(CellAbove,A2)- Select B2. While B2 is selected, press Ctrl+Alt+F3, enter CellAbove as the name, in the refers to box, enter this formula:
=!B1
Click OK.
