Getting Formulas to Stick in Google Spreadsheets

I admit I overuse Google Spreadsheets - I use it for all my estimates, tracking tasks, many things I should use much better task-specific tools for.

With that admission out of the way, my overuse leads me to frequently setting up a spreadsheet like this, where some number of columns are being summed into row 2. The obvious way to sum something like that is:

=sum(B3:B1000)

Then hope you never hit 1000 rows. Smart spreadsheeters will note you can do better than hope with a bit of a weirdo syntax:

=sum(B3:B)

Which sums everything from B3 down, infinitely. But that still doesn't solve the problem I run into: Someone, sometimes me, adds a row to the top of the spreadsheet, right at row 3, and Google Spreadsheets unhelpfully, silently, "fixes" my formula for me:

=sum(B4:B)

My sum is now off by just a little, and if it's a big spreadsheet I am not going to notice. Terrible things have come of this. Strangely Google Spreadsheets even screws up attempts to prevent this, like =sum(B$3:B) - it still "fixes" the range when you add a row, breaking the range. So, to solve it, you basically have to trick Google Spreadsheets into not trying to be smart, with an indirect function:

=sum(indirect("B3:B"))

By stuffing the cell range into a string and then using indirect to pass that to sum, you can add rows to the top all you like, and the B3:B range holds. Take that Spreadsheets.