• 462K Members
• 5,940 Online
• 559K Conversations
SOLVED

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

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

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

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

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?

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.

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

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

You’re welcome.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies