Forum Discussion
Solved
Inserting a row in a table
I've created a table which resembles a checkbook register. That is the first line is the starting balance and the next line is the starting balance adjusted by the value in the current line. This is what it would look like
Now if I add a line between 18 and 19 the formulas look like this
How do I get the formulas to update and be correct so that the result looks like this
sammary That's because you are mixing structured references with direct cell references. Try it this way:
sammary The most reliable method for creating a running balance in a table ledger like this is to first define a relative workbook name for the "Cell Above". To do this, start by selecting cell D18. Then, on the Ribbon, go to the Formulas Tab > Defined Names Group > Define Name. In the New Name dialog box, for the Name field, type CellAbove, and for the Refers To field, type =!D17, as shown below:
Define Name: CellAbove
Note: the cell referenced is relative to the active cell... be sure to remove any dollar signs ($) that may appear automatically, as well as the sheet name, but leave the exclamation point (!).
Then, use the following formula to create a calculated column for the running balance:
=SUM(CellAbove, [@Amount])The SUM function successfully ignores the text string "Balance" for the first row in the table, whereas =CellAbove+[@Amount] would have returned the #VALUE! error. Cheers!
sammary That's because you are mixing structured references with direct cell references. Try it this way:
