Pivot Tables, refreshing with additional data

[Jim Power]

This is probably obvious, but I can't figure it out or find it anywhere.

I have a pivot table. At its simplest, it has "Ink Types" in rows,
"Date" in Columns and Sum of "QTY" as data. I have grouped the Date
fields by Month and Year This pivot table is based on a table on
another sheet that contains specific sales data, with the date in
mm/dd/yy format.

If I select the specific rows in the data table that contain data, I can
create my pivot table and everything is hunky-dory. However, I want to
keep adding to this table, as new sales are posted. So I want the pivot
table to accept all the data in that table, however many rows there are.
However, if I select the columns in the data table (A:F: rather than
A1:F127) my grouping screws up. I assume this is because there are now
blanks in the table and Excel needs values in every date field in order
to group.

So - what can I do to fix this?

Thanks,
-Jim

 

[Ed Ferrero]

Hi Jim,

Use a dynamic range. Assuming the data is in Sheet1, use menu item
Insert-Name-Define enter 'data' as the name and enter this formula
in the Refers to box.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),6)

Then enter =data as the data range for the pivot table.

The 'data' range will calculate to include all rows in cols A:F that contain
values in column A (don't insert blank rows or the date
grouping will fail)

Check out the pivot table tutorial on my website for more
information.