Forum Discussion
How can I get a formula to NOT update when I move some of the target data?
If you use Cut and Paste to move a value the formula in another cell will follow the target cell to its new location. If you use Copy and Paste, it won't. But you'll have to go back and delete the value in the original cell.
Why does this happen? With Cut and Paste, the assumption is that you're reorganizaing the layout of the sheet. (I say as a layman in the field; I don't actually know the "deep state" reason)
But I have a question for you: WHY would you need to move a value from Column C to some other spot, even another row in Column C? If you're just wanting things always to be in date sequence--which I suspect might be the reason, you could do a Data....Sort of the entire range of cells from row 7 on down, and the series of formulas remains functional as you want it, connecting to the adjacent cell and the one above. To do that Sort routine neatly would mean you should be entering actual dates in one of the columns. I did a sort on random numbers in Col A in the attached, to show you.
But you really don't need to do that Sort routine either. If you're really developing a budget spreadsheet here, not merely a checkbook balancer, you're going to want to avail yourself of something like the Pivot Table function to create a summary report of expenses by category by month.....and then you won't need individual entries in date order anyway. If that's something of interest, write back and we can refer you to some templates or samples...
Care to learn about a very useful formula for such cases, the formula name is INDIRECT.
If for example instead of =C6 , you use =INDIRECT("C6") , then always excel will pick value from C6 even if C6 cell is moved/cut pasted somewhere else.
Similarly, formula =INDIRECT("C8")+D7 means that always C8 will be fixed but if you move D7 cell, then your entered formula would change to follow it's new position. If you want D7 also to be fixed, then enter
=INDIRECT ("C8")+INDIRECT("D7")
This would solve your problem.
However take care:-
1) put the address in INDIRECT function in quotes " "
2) since people are habitual of the behaviour that when a cell is moved then formulas follow that cell, so using INDIRECT formula can surprise users as excel would behave differently by not following the moved cell. If only you would use your excel file, then it doesn't matter. This alert is only if you give your file to someone else to use.
3) Note that tomorrow if you insert some new rows at top to say write more headings etc., then your formulas would still continue to refer to "C8" , this can ruin your calculations.
So in the end excel is designed to adjust formulas if cells move for better behaviour. Although it gives you an option to use INDIRECT formula if you want to change that behaviour, but if users are new or you yourself forget that you used INDIRECT, then this changed behaviour can also backfire!
- Thanks, amit_bhola. I have wished for that one for many years!
