Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2752479%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752479%22%20slang%3D%22en-US%22%3EHow%20can%20I%20prevent%20the%20cell%20reference%20in%20my%20formula%20from%20shifting%3F%3CBR%20%2F%3EI%20have%20a%20worksheet%20with%20columns%20from%20A%20to%20P%2C%20My%20IF%20formula%20on%20the%20G%20column%20is%20%3DIF(J2%3DP2%2C%22No%20Change%22%2CIF(AND(J2%3D4%2CP2%26lt%3B4)%2C%22New%204%22%2CIF(AND(J2%3D5%2CP2%26lt%3B5)%2C%22New%205%22%2CIF(AND(J2%26lt%3B4%2CP2%26gt%3B3)%2C%22No%20Longer%204%20or%205%22%2CIF(J2%26lt%3B4%2C%22Not%204%20or%205%22)))))%20when%20I%20add%20a%20new%20column%20before%20J%2C%20all%20the%20data%20from%20columns%20J%20to%20P%20will%20shift%20as%20well%20as%20my%20formula%20will%20shift%20the%20cell%20references%20to%20%3DIF(K2%3DQ2%2C%22No%20Change%22%2CIF(AND(K2%3D4%2CQ2%26lt%3B4)%2C%22New%204%22%2CIF(AND(K2%3D5%2CQ2%26lt%3B5)%2C%22New%205%22%2CIF(AND(K2%26lt%3B4%2CQ2%26gt%3B3)%2C%22No%20Longer%204%20or%205%22%2CIF(K2%26lt%3B4%2C%22Not%204%20or%205%22)))))%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2752479%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2752577%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2752577%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1156583%22%20target%3D%22_blank%22%3E%40Zebari480%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUse%20the%20symbol%20%24%20before%20the%20column%20letter%20in%20your%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2753444%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2753444%22%20slang%3D%22en-US%22%3EThanks%20for%20that%20but%20the%20%24%20doesn%E2%80%99t%20have%20an%20effect%3C%2FLINGO-BODY%3E
New Contributor
How can I prevent the cell reference in my formula from shifting?
I have a worksheet with columns from A to P, My IF formula in column G is =IF($J2=P2,"No Change",IF(AND($J2=4,P2<4),"New 4",IF(AND($J2=5,P2<5),"New 5",IF(AND($J2<4,P2>3),"No Longer 4 or 5",IF($J2<4,"Not 4 or 5"))))) when I add a new column before J, all the data from columns J to P will shift as well as my formula will shift the cell references to =IF(K2=Q2,"No Change",IF(AND(K2=4,Q2<4),"New 4",IF(AND(K2=5,Q2<5),"New 5",IF(AND(K2<4,Q2>3),"No Longer 4 or 5",IF(K2<4,"Not 4 or 5")))))
4 Replies

@Zebari480 

Use the symbol $ before the column letter in your formula

Thanks for that but the $ doesn’t have an effect
Excel shifts the position and reference when you add or delete column or row. It helps to keep relative reference. If your data is on column J and suddenly another column is add before J, it "shifts to the right" moving your reference to the next column. It makes sense because you are adding a column before that column. If you want to keep absolute reference on your formula you need to create another formula INDIRECT("YourCellReference")
=IF(INDIRECT("J2")=INDIRECT("P2").....
Thank you Juliano, it works now, however I have 87,000 lines so the row reference numbers are not changing when I drag the formula down, do you have a trick to overcome this?