 SOLVED

Highlighted
New Contributor

# 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 ?

6 Replies
Highlighted

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

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

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

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-

Highlighted

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

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?

Highlighted
Solution

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

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.
Highlighted

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

Now, this is realy working, thank you!

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

Highlighted

You’re welcome.