05-10-2020 12:33 PM
05-10-2020 12:33 PM
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?
05-10-2020 02:43 PMSolution
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...
05-10-2020 04:46 PM
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.
05-10-2020 07:45 PM
05-11-2020 08:44 AM
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.
05-11-2020 08:50 AM
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.
05-11-2020 08:52 AM
@amit_bhola Thanks for the suggestion. I will give INDIRECT a try.
05-11-2020 08:52 AM
05-11-2020 09:01 AM
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.....