SOLVED

Relative cell reference problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2357862%22%20slang%3D%22en-US%22%3ERelative%20cell%20reference%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357862%22%20slang%3D%22en-US%22%3E%3CP%3ECompletely%20new%20to%20Excel%20formulas.%20I'm%20using%20Excel%202010%20on%20Windows%2010%2064-bit.%20I%20am%20trying%20to%20do%20a%20simple%20add%20and%20subtract%20operation%26nbsp%3B%20using%20a%20formula%2C%20but%20when%20I%20copy%26nbsp%3B%20and%20paste%20the%20formula%2C%20it%20does%20not%20update%20the%20row%20reference.%20The%20formula%20in%20question%20is%20%3DSUM(%24G2-%24E3%2B%24E3).%20My%20understanding%20is%20that%20the%20%24%20makes%20the%20column%20references%20absolute%20but%20the%20row%20references%20should%20update%20when%20copied%20and%20pasted.%20They%20do%20not.%20I%20have%20my%20calculation%20options%20set%20to%20automatic.%20I%20don't%20know%20what%20else%20to%20check.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2357862%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-2357884%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20cell%20reference%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357884%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1054863%22%20target%3D%22_blank%22%3E%40rwadleigh%3C%2FA%3E%26nbsp%3BYour%20understanding%20of%20the%20workings%20of%20the%20%24%20is%20correct.%20When%20you%20drag%20or%20copy%2Fpaste%20a%20cell%20%3CU%3Edownwards%3C%2FU%3E%2C%20the%20row%20references%20should%20update%20automatically.%20You%20are%20not%2C%20by%20any%20chance%20copying%20from%20the%20formula%20bar%3F%20Because%20then%20the%20updating%20does%20not%20happen.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20mentioned%20to%20be%20completely%20new%20to%20Excel%20formulas%2C%20allow%20me%20to%20comment%20on%20the%20formula%20in%20question.%20You%20don't%20need%20the%20SUM%20function%20and%20it%20isn't%20really%20very%20meaningful.%26nbsp%3B%3CSPAN%3E%3CU%3E%3CSTRONG%3E-%24E3%2B%24E3%3C%2FSTRONG%3E%20%3D%20zero%3C%2FU%3E%20and%20can%20thus%20be%20omitted.%20You%20may%20use%20%3CSTRONG%3E%3D%24G2%20%3C%2FSTRONG%3Eto%20achieve%20the%20same%20outcome.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2357889%22%20slang%3D%22en-US%22%3ERe%3A%20Relative%20cell%20reference%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2357889%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3EThank%20you%20for%20your%20solution.%20I%20was%20indeed%20copying%20from%20the%20formula%20bar.%20I%20will%20also%20follow%20your%20advice%20for%20simplifying%20the%20formula.%20And%20finally%2C%20I%20mistyped%20the%20expression%20in%20my%20help%20request.%20It%20should%20have%20read%20%3DSUM(%24G2-%24E3%2B%24F4)%20soon%20to%20be%20%24G2-%24E3%2B%24F4.%20Thanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Completely new to Excel formulas. I'm using Excel 2010 on Windows 10 64-bit. I am trying to do a simple add and subtract operation  using a formula, but when I copy  and paste the formula, it does not update the row reference. The formula in question is =SUM($G2-$E3+$E3). My understanding is that the $ makes the column references absolute but the row references should update when copied and pasted. They do not. I have my calculation options set to automatic. I don't know what else to check.

5 Replies
best response confirmed by rwadleigh (New Contributor)
Solution

@rwadleigh Your understanding of the workings of the $ is correct. When you drag or copy/paste a cell downwards, the row references should update automatically. You are not, by any chance copying from the formula bar? Because then the updating does not happen.

 

Since you mentioned to be completely new to Excel formulas, allow me to comment on the formula in question. You don't need the SUM function and it isn't really very meaningful. -$E3+$E3 = zero and can thus be omitted. You may use =$G2 to achieve the same outcome.

 

@Riny_van_EekelenThank you for your solution. I was indeed copying from the formula bar. I will also follow your advice for simplifying the formula. And finally, I mistyped the expression in my help request. It should have read =SUM($G2-$E3+$F4) soon to be $G2-$E3+$F4. Thanks again.

I continue to be careless in my edits. Formula should be $G2-$E3+$F3.
A new but related problem. I have a series of consecutive table rows with this formula. I have to add a row back in the middle of what I've already entered. The usual copy/paste works on that row, but the references in the following rows don't update. How do I get the rest of the table to increment their row references?

@rwadleigh "Add a row back"?? Not sure I follow. Can you upload an example of what you are trying to achieve and indicating what doesn't seem to work?