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