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