The Auto-Total

Frequently I find myself keeping tables with rows of data, and wanting a sum at the bottom of the table. For example, I have an expense claim form that looks something like this:

Originally I added the total just as I would have done in any other spreadsheet: by putting =SUM(B2:B3) into cell B4. However, this meant that in order to add a new item, I had to remember to either insert a new row above row 2 or row 3. If I inserted a row above row 4, Resolver One - just like any other spreadsheet program - did not know that it needed to extend the range in the sum, so the new expense item wasn't added to the total. With expense claims, this is a Bad Thing for me and for my bank balance.

Having managed to under-claim my expenses a number of times, I realised that with Resolver One you can avoid this problem by not adding a total at all in the grid, and instead doing it with a bit of code.

First, I set row 1 as a header row. Next, I added the following to the post-formulae user code:

sheet = workbook["Sheet1"]
total = SUM(sheet.Cols["Cost"])
totalRow = sheet.MaxRow + 2
sheet["Item", totalRow] = "Total"
sheet["Cost", totalRow] = total

Now, when I want to add another item to my expenses, I don't even need to insert a row - I just go to the blank line between the last item and the existing total and start typing.