Pivot table auto update column

Hi all,

I'm trying to have my pivot table auto update. The problem I'm running into is when I add a new column every week. i.e week1, 2, 3, etc.

I tried naming a range and using the formula below, but it does not work past any populated column (currently column 6). Once I insert a seventh column it will not pick it up.
=Sheet1!$A$1:INDEX(Sheet1!$A:$B,COUNTA(Sheet1!$A:$A),6)

I attached a mock report to help.

btw I'm using 2010.

thanks

One problem is the way you are entering your information. A pivot table can be better organized by using lists. Use one column with the title "Week" and put the week number in it. Use a fourth column for the actual count. This lets you set up a pivot table doing more like you want.
See the attached for examples.

PivotTable.xls

Great solution.
Thank you very much.