Dynamic Pivot Table Probems

[db1966]

Here goes - this might take some explaining:-

I have a spreadsheet the includes data validation, auto fill fields using formulas and conditional formatting all over the place. The user will be adding rows of data whenever applicable, so I want to create a dynamic pivot table that will update automatically. I think I've done it properly, Insert - Name - Define. In the Refers To field, I've added:-

=OFFSET(Sheet1!$A$10,0,0,COUNTA(Sheet1!$A:$A),12)

I've then based the Pivot Table on the defined name. For some reason, when I add a new row of data, the pivot table doesn't update, I have to do it manually. When I go back into Sheet1 and click in the Refers To field, there is an extra "blank" row showing as included in the data range (screen print attached) Is my OFFSET wrong, or does it have anything to do with the fact each row (down to line 300) contains formulas and #N/A's? I've used this before and can't see what I'm doing differently, other than the aforementioned formulas etc.

Any help/advise appreciated