Forum Discussion

sammary's avatar
sammary
Copper Contributor
Nov 23, 2023
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 ...
excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Nov 23, 2023

    sammary That's because you are mixing structured references with direct cell references. Try it this way:

     

djclements's avatar
djclements
Silver Contributor
Nov 23, 2023

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!

Resources