We have a spreadsheet like the example below. Each item has one or more lines with numbers and then a total. As things change over time, we need to add and delete rows. When the number of rows gets down to only one, as with "Oak", the sum formula's range becomes a single cell like in row 9 of this example. Later when we add a new row to "Oak" we have to stop and fuss with editing the formula. Or, if we add a new row to Maple going between the current rows 3 and 4, the range won't expand to include it.
In real life, our spreadsheet has dozens of columns like "C" in the example, so when we fix the sum formula, we must drag it rightward along the row to make all the columns work properly. We'd like the user of the spreadsheet to not have to remember or spend the time fixing these sum cells. What do experienced spreadsheet users recommend? Is there a way to avoid having the range collaps when there's only one row, or to include new rows automatically in the sum's range?
A B C
1 Maple
2 10
3 45
4 =sum(D2:D3)
5
6 Oak
7 22
9 =sum(C7)
In real life, our spreadsheet has dozens of columns like "C" in the example, so when we fix the sum formula, we must drag it rightward along the row to make all the columns work properly. We'd like the user of the spreadsheet to not have to remember or spend the time fixing these sum cells. What do experienced spreadsheet users recommend? Is there a way to avoid having the range collaps when there's only one row, or to include new rows automatically in the sum's range?
A B C
1 Maple
2 10
3 45
4 =sum(D2:D3)
5
6 Oak
7 22
9 =sum(C7)