Have formulas calculate new rows

Hi,

Yes, you should always use range names to identify ranges and a special variation called a dynamic range name where the number of data rows or columns change.
I'd also consider putting my summary formulae above the data since these will be fixed rows and you don't need to concern yourself with adding new rows just to move everything down.

Assuming your data is currently in say A10:M100 then a range name called say 'Data' which always covered the data range would be say

Formula:

Please Login or Register  to view this content.

Similarly you could create a dynamic range name for a single column so that you could use the range name in say a SUMIFS function. So supposing column A were dates and amounts were column M then a dynamic range name called lst_Dates would be defined

Formula:

Please Login or Register  to view this content.

and similarly for lst_Amounts

then your SUMIFS, (or SUMPRODUCT if there's some particular reason to prefer that to a SUMIFS) would be =SUMIFS(lst_amounts,lst_Dates,TODAY())

To add to what Richard already mentioned, and depending on your formula, a less advanced method than Named Ranges would be to add in a place holder row between your last record and your calculating formulas, whose range extends through the place holder row. Whenever you need to insert a new row for records, insert above the place holder row (which your calculating formulas would still capture).

Granted, this is a less-elegant solution than Richard's, but one I have used numerous times in a pinch when I didn't feel like dealing with Named Ranges.