SOLVED

Define the reference cell as being always the above one, even when I copy/paste the row

Copper Contributor

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 ?

6 Replies
Select B2. Define CellAbove with this formula:
=!B1
Enter this formula in B2, copied down rows:
=SUM(CellAbove,A2)

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

 

@AniB03-

@Twifoo 

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?

best response confirmed by AniB03- (Copper Contributor)
Solution
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.

@Twifoo 

Now, this is realy working, thank you!

I also tried to do the same thing in Google SpreadSheets, but it is not working. 

1 best response

Accepted Solutions
best response confirmed by AniB03- (Copper Contributor)
Solution
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.

View solution in original post