Pivot table - refresh new rows?

[kiwi]

How can I get a pivot table to dynamically refresh/accept new rows that are added to the worksheet? The 'refresh data' option only seems to update the pivot table when existing data rows have been changed.

Thank you

[kiwi]

Don't worry, I found the answer - using named ranges and in the 'Refers To' box typing OFFSET(=OFFSET($A$1,0,0,COUNT($A:$A),1))

I found this on the message board referencing this site:

http://www.ozgrid.com/Excel/DynamicRanges.htm#OFFSET

[kiwi]

Oops I meant

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1A:$A),16)

[Aladin Akyurek]

On 2002-04-27 05:30, kiwi wrote:
How can I get a pivot table to dynamically refresh/accept new rows that are added to the worksheet? The 'refresh data' option only seems to update the pivot table when existing data rows have been changed.

Thank you

Create a dynamic name range, say, Data, and give this as Range to your already created pivot table (PT):

Activate a cell within your PT.
Activate PivotTable Wizard that gives you a window entitled "PivotTable and PivotChart Wizard - Step 3 of 3".
Activate the Back button, which gives you "Step 2 of 3", showing Range with its current value.
Replace that current value with dynamic name range.

Next time to refresh while new rows added to your data, you'll see them included.

If you don't know how to create a dynamic name range, post the current range address of your data along with the kind of data (numbers, dates, or text) the range houses and the name of the sheet where your data is located.

Aladin

[Aladin Akyurek]

On 2002-04-27 06:28, kiwi wrote:
Oops I meant

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1A:$A),16)

You have a typo in that formula:

=OFFSET(Sheet1!$A$1,0,0,COUNT(Sheet1!$A:$A),16)

By the way, if there is ever a blank cell in A, this formula will cause havoc. Change it as follows to cover the problem with possible blanks:

=OFFSET(Sheet1!$A$1,0,0,MATCH(9.99999999999999E+307,Sheet1!$A:$A),16)

Aladin