Forum Discussion
Bank Reconciliations
I'm guessing that you are NOT working with a structured table. Let's assume that you have a Total formula =SUM(G1:G49) in G50. Then you can't insert row 50 and expand regular SUM function automatically to include the new row 50.
One way would be allow for a growing sum range by including an empty row. So, in the above, example, row 49 would be empty and then you insert a row above it. Then the sum range becomes G1:G50.
A formula solution would be to insert an OFFSET in the SUM function like this:
=SUM(G1:OFFSET(G50,-1,0))
Now you can insert a row above row 50 and the OFFSET will make sum range to end one row above (the -1) the row where the formula sits.
Alternatively, use a structured table with a Total row. That will allow you to insert a row below the last row with data and the sum will update automatically.
Riny, thanks for the quick response. Your assumption about not using a "structured table" is correct.
For clarity let me tell you the purpose of the spreadsheet.
It is to be used by a "lay person/novice" to enter extra Row items to Income or Expenses beyond the number of Rows I would create initially. i.e. when they add an extra Row then the Total Row will adjust automatically - they create the extra Row, enter their data in each cell and the Totals adjust automatically.
The spreadsheet has a Details column, 12 columns January to December, then a Row Total column, then a % column (each Row Total is a % of the Grand Total (Income or Expense). Obviously each Row has its own Total for the month in question.
The end objective is that the user will create as many Rows as they want to enter their data, populate the cells with the values for each Item for that month and all totals will automatically adjust.
I want to be able to lock the Cell Formulas so the user cannot contaminate the spreadsheet. The user has two opportunities - create a new Row, populate cell values, the spreadsheet does the rest.
I hope this clarifies what I am seeking.