I have numbers in cells C1, D1, E1, numbers are 1,2,3 respectably. Formula in cell A4 is =sum(C1:E1) and it's returning 6. When I select cells C1 and D1 and cut and paste in cell B1 formula is changing it range to B1:E1. I'm not touching formula it self, just changing cell values. I do not want that, formula should stay the same =sum(C1:E1). I have also tried with absolute references; =sum($C$1:E$1$) but when I cup/past values formula is also changing.

Is there any way to stop this behavior. Moving data between cells should not change reference of formulas in other cells.

  • 2
    Try: =SUM(INDEX(1:1,3):INDEX(1:1,5)) – Scott Craner Aug 12 at 19:00
  • Nope sir, not working. – IGRACH Aug 12 at 19:05
  • 1
    It works for me. I can cut and paste and the reference always remains on those three cells. – Scott Craner Aug 12 at 19:06
  • I was able to solve it with indirect function. – IGRACH Aug 12 at 20:40
  • 1
    Beware the INDIRECT is volatile and forces a recalc whenever Excel recalcs and too many of them will slow down the calc times. If the INDIRECT worked and INDEX did not then you are not using Excel. – Scott Craner Aug 12 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.