I have a database-like table in Excel 2003, with a single header row containing AutoFilters in row 6, and data from row 7 to -say- row 160. Rows 1 - 5 are the sheet title, column group titles and instructions I cannot get rid of.
I also display the SUM(X6:X160) and SUBTOTAL(109,X6:X160) in this area for numeric columns. I don't want to display this at the bottom of the table because several users add rows to that table frequently and they destroyed the formulas on a regular basis.
Problem: whenever a user adds data to the end of the table, one would need to update the SUM and SUBTOTAL formulas to expand the range which - needless to say - is mostly forgotten.
I could myself extend the formulas to cover all rows up to -say- row 500 and hide the remaining rows, so a user would need to "insert" rows when the visible end of the table is reached - which in turn would update the formulas, but I don't regard this as a very safe way ... knowing my users.
Question: Is there a way to create SUM and SUBTOTAL from X6 "to the end of column X wherever that is"?
CONCLUSION
I used SUM(OFFSET(....)), taking the "height" parameter from a new field in the header that displays the "number of records" by a =COUNTA($A:$A)-1 (-1 for the column heading text that is counted as well), as it is clear from the business context that a key value must exist in column A for any valid data record and no blank rows are allowed (I can train users that much at least) - plus the user benefit of not only seeing the SUM but now as well the COUNT of records in the header frozen pane.