Forum Discussion
Excel
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")))))
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
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").....
