SOLVED
Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-561479%22%20slang%3D%22en-US%22%3EDefine%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561479%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20folowing%20formula%20for%20B2%26nbsp%3B%20%26nbsp%3B%20%3DB1%2BA2%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20rows%20go%20until%20for%20example%2099%2C%20with%20the%20same%20formula%20on%20B%20column.%20But%20I%20sometimes%20Copy%2FPaste%20rows%20-%20for%20example%20row%202%20to%20row%2098.%20In%20this%20case%2C%20my%20formula%20should%20be%20%3DB97%2BA98.%20Instead%2C%20it%20remains%20B98%26nbsp%3B%20%3D%3CSTRONG%3EB1%3C%2FSTRONG%3E%2BA98%3C%2FP%3E%3CP%3EHow%20can%20I%20define%20the%20referece%20cell%20as%20being%20always%20the%20above%20one%2C%20no%20matter%20where%20I%20move%20the%20row%2Fposition%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-561479%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561530%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561530%22%20slang%3D%22en-US%22%3ESelect%20B2.%20Define%20CellAbove%20with%20this%20formula%3A%3CBR%20%2F%3E%3D!B1%3CBR%20%2F%3EEnter%20this%20formula%20in%20B2%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DSUM(CellAbove%2CA2)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561546%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561546%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20cell%20references%20in%20the%20formula%20need%20to%20be%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-ie%2Farticle%2Fswitch-between-relative-absolute-and-mixed-references-dfec08cd-ae65-4f56-839e-5f0d8d0baca9%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Erelative%3C%2FA%3E%20which%20is%20the%20default.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20formula%20you%20have%20in%20cell%20B2%20is%20completely%20relative%3A%3C%2FP%3E%3CPRE%3E%3DB1%2BA2%3C%2FPRE%3E%3CP%3ETo%20copy%20it%20all%20the%20way%20down%2C%20use%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DkgGF136-kIg%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFill%20Handle%3C%2FA%3E.%3C%2FP%3E%3CP%3EYou%20will%20find%20the%20Fill%20Handle%20in%20the%20right-hand%20corner%20of%20the%20cell.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20lock%20a%20cell%20in%20a%20formula%20so%20that%20it%20doesn't%20move%20when%20you%20copy%20the%20formula%20down%2C%20you%20need%20the%20use%20the%20absolute%20reference.%3C%2FP%3E%3CP%3ETry%20this%20example%3A%3C%2FP%3E%3CPRE%3E%3DB1%2B%24A%242%3C%2FPRE%3E%3CP%3EWhen%20you%20copy%20this%20formula%20down%2C%20the%20cell%20A2%20will%20not%20change.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340875%22%20target%3D%22_blank%22%3E%40AniB03-%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561590%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561590%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20taking%20your%20time%20to%20answer%20me.%3C%2FP%3E%3CP%3EI%20copied%20in%20B2%20your%20formula%3A%26nbsp%3B%3DSUM(CellAbove%2CA2)%20but%20it%20returns%20ERROR.%20Thou%20I%20did%20not%20do%20anything%20with%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%22Select%20B2.%20Define%20CellAbove%20with%20this%20formula%3A%3C%2FSPAN%3E%3CSPAN%3E%3D!B1%22%20part.%20What%20do%20you%20mean%20by%3A%20-Define%20CellAbove%20with%20this%20formula%3A%3D!B1%3F%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561606%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561606%22%20slang%3D%22en-US%22%3ESelect%20B2.%20While%20B2%20is%20selected%2C%20press%20Ctrl%2BAlt%2BF3%2C%20enter%20CellAbove%20as%20the%20name%2C%20in%20the%20refers%20to%20box%2C%20enter%20this%20formula%3A%3CBR%20%2F%3E%3D!B1%3CBR%20%2F%3EClick%20OK.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561619%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561619%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20this%20is%20realy%20working%2C%20thank%20you!%3C%2FP%3E%3CP%3EI%20also%20tried%20to%20do%20the%20same%20thing%20in%20Google%20SpreadSheets%2C%20but%20it%20is%20not%20working.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-561662%22%20slang%3D%22en-US%22%3ERe%3A%20Define%20the%20reference%20cell%20as%20being%20always%20the%20above%20one%2C%20even%20when%20I%20copy%2Fpaste%20the%20row%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-561662%22%20slang%3D%22en-US%22%3EYou%E2%80%99re%20welcome.%3C%2FLINGO-BODY%3E
AniB03-
New 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?

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. 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies