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.

Comments

If you have comments, questions or suggestions about any of the Resolver One documentation, please post them to the Documentation Suggestions Forum.