How do I prevent Calc from changing formulas when I move, insert or delete cells or rows?
Example:
- Open/Create a new sheet.
- In B2, enter value of 1.
- In B3, enter value of 2
- In A1, enter formula =B2+B3
- Cut B2 and paste in B1.
Suddenly, Calc updates the formula in A1 to =B1+B3!
A similar thing happens if I insert a row: Calc adjusts every since formula that references the old row in order to prevent the calculated value from changing.
Similar to this question, except that deals only with formulas changing when sorting occurs. That is not what I am concerned about.
Here’s why this is a big problem: I use Calc to balance various account balances, with accounts running columns and dates of the months running down rows. I initially have transactions I expect to occur, but whenever an unexpected transaction occurs, I need to insert a new row. But unless I go through and verify every individual formula in each balance column, inevitably the formula gets all messed up. Obviously this is a big balance if I rely on the projected future balance to make sure I don’t overdraw my account due to the formula being wrong because Calc changed it and I failed to catch it. (Copying and pasting a known good formula is the most reliable, but isn’t foolproof because I have different formatting and monthly value resets which prevents me from doing a blanket copy/paste down the whole column.)
Update:
The problem is a bit trickier than simply preventing Calc from changing a formula. This can be accomplished as follows:
First, set up all the formulas the way you want them. For instance, for a running balance column, create the first formula, then copy and paste it down the column as far as needed.
Select all the cells with formulas to fix, and navigate the menus to Edit:Find and Replace.
Search For: \$?([A-Z])\$?([0-9]+)
Replace With: INDIRECT("$1$2")
Other Options:
Check “Current selection only”
Check “Regular Expressions”
Search in: “Formulas”
Now click the “Replace All” button.
But this only protects against the formula from changing if a cell it references moves. If a row is inserted, not changing the formula is incorrect as well. For instance, suppose I have a running balance, where column A contains the balance, and column B contains the transaction amount. Suppose that the formula for B147 is “B146+C147”. If I insert a row between row 146 and 147, shifting 147 to 148, then “B146+C147” (an unchanged formula) is wrong because it no longer gives the correct balance in the correct spot. But if the formula isn’t protected, Calc changes the formula for B148 (formerly B147) to “B146+C148”. THIS IS WRONG TOO! It totally ignores the new row 147: the formula needs to change to “B147+C148”, and the new B147 needs to get “B146+C147” which is the original formula for B147. Trying to keep all this straight manually is turning out to be a nightmare. How can I make Calc do this automatically for both row insertion and cell move?