How can I get a formula to NOT update when I move some of the target data?

[RC_in_MN]

I built a simple budget spreadsheet that keeps track of my account balance. Currently, if I move the target data, the formula next to it wants to follow that data. How can I prevent that from happening so the formula in D8 always refers to C8, even if I move the data in C8 to a different location and replace it with something new?

[#2 · Accepted solution]

@RC_in_MN 

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...

[#3]

@RC_in_MN 

Here's an example of a Pivot Table summary report drawn from a list of expenses and categories. Solely shared for the purpose of showing how that method works to summarize data. Your input sheet could be enhanced just slightly--we can exchange messages about that if you're interested--to collect your category for each expense, doing so based on a drop down list you specify. I've created my own expense tracking spreadsheet recently, incorporating multiple bank and credit card accounts..... The Pivot Table is a very popular, and easy to use, built-in way Excel can produce very helpful cross-tabulated reports like this.

[#4]

Hi,
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!

[#5]

Thanks, @mathetes!

🤦🏼‍ Copy+paste instead of cut+paste was a revelation. Thank you!

Also putting the dates in column A and sorting that way will work fine. In this case, all I want to do is project account balances, so that will solve my problem beautifully.

Thank you!

[#6]

Thanks again for the input. 

For the purpose of this spreadsheet, I think you solved it for me—thanks!

I *may* be able to use the Pivot Table function to help me replace a project estimating form I currently have in FileMaker Pro. It works great there, but—since it's the only thing I do in FMP—I may recreate it in Excel, simply to save the cost of updating FileMaker when I hardly use it. That will come later.

Thanks again. 

[#7]

@amit_bhola Thanks for the suggestion. I will give INDIRECT a try. 

Kind regards....

[#8]

This:
B5: =INDEX($1:$1048576,5,1)

Can be copy/pasted anywhere without throwing the #REF! error.

[#9]

Thank you!

[#10]

@RC_in_MN 

Where in MN, just out of curiosity? I went to college in MN (Northfield)...haven't lived there, however, for well over half a century now.

Back to the subject at hand: the Pivot Table is an incredibly useful tool that Excel has had for decades now. Easily summarizes a data table across rows and columns. Often used for the kind of budget category expenses by month, it can also do any other cross-tabulated report. For example, if your project estimating has to do not with costs by calendar entry (month/week/year) but rather, sub-project by contractor.....

[#11]

@mathetes thanks a lot!!

I did a lot of research for something so simple.

Just to comment: the feature works for small data ranges but for a user that compares large data ranges does not make sense, it is simpler to insert a new value and push cells down without the formula being updated. Select the data range copy/paste, and after that inserting the new value is a waste of time.

Thanks again.

[#12]

I'm not sure what "feature" you're referring to, the comment coming as it does two years after the earlier part of the thread. It sounds like you're not really asking a question, so no need to reply....but for the sake of others who might stumble on the thread, your comment would make more sense if you specifically named the feature you're addressing.

[#13]

Thanks, amit_bhola. I have wished for that one for many years!