If I have a spreadsheet that sums a column of the rows above it, I obviously have to refer to the starting row/cell, say A3. I then refer to the ending row/cell, say A10.
My problem is that my users will insert new rows above row 3, but my formula says start at A3. If there is a value to be summed in A2, it's ignored because the formula starts with A3.
The same problem occurs when the users insert a row below row 10; the value in row/cell A11 is ignored. I know Excel can adjust if they insert a row in between the values in the formula, for example a new row above row 6. But they can't be counted on to do that, despite my entreaties.
I don't want a VBA solution; I'm sure there is a function I'm missing. I've looked at Dynamic Ranges, but I don't think that solves my problem.
So how can I sum all the rows that have user data without checking the formula each time I publish the spreadsheet?
I'm using Excel 2007, but will be switching to 2013 in a day or two.
I know this has to be one of the dumbest questions on the forum!![Frown :( :(](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f641.png)
Thanks so much for your help!
My problem is that my users will insert new rows above row 3, but my formula says start at A3. If there is a value to be summed in A2, it's ignored because the formula starts with A3.
The same problem occurs when the users insert a row below row 10; the value in row/cell A11 is ignored. I know Excel can adjust if they insert a row in between the values in the formula, for example a new row above row 6. But they can't be counted on to do that, despite my entreaties.
I don't want a VBA solution; I'm sure there is a function I'm missing. I've looked at Dynamic Ranges, but I don't think that solves my problem.
So how can I sum all the rows that have user data without checking the formula each time I publish the spreadsheet?
I'm using Excel 2007, but will be switching to 2013 in a day or two.
I know this has to be one of the dumbest questions on the forum!
![Frown :( :(](https://cdn.jsdelivr.net/joypixels/assets/8.0/png/unicode/64/1f641.png)
Thanks so much for your help!