I think my answer here: In Excel 2010, how can I create a template with a function automatically ends in the last cell with data? may be of use.
I strongly recommend tables if you're able, they also have a built in total row function.
If not fall back on the dynamic named range.
EDIT
As we've discussed, you want to be able to use a dynamic named range as the source range for a pivot table:
This requires some slight modification to the dynamic named range. Firstly you'll need your range to include your column titles, so the pivot table has headings, and then you will need to expand your range across all relevant columns.
=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F$1:$F$12),COUNTA(Sheet1!$F$1:$G$1))
Here you can see that instead of ending the formula with ),)
we've added a column reference that returns the range of columns by covering the 1st row across all needed columns.
When you insert a pivot table use the name of this range.
NOTES
Wrapping the OFFSET in a COUNT of any kind won't work if you want to use the named range in a pivot table, but it isn't necessary, as you can just run the count on the pivot table.
If you and your users are all using 2007 or up then use a table instead, this will remain dynamic even if you add more columns, and will never require you to go in a and increase the height of the named range. You will still be able to refer the table to a pivot table.
range
? Are you trying to define a named range that is dynamic that you can use in other formulas?