View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Roger Govier[_8_] Roger Govier[_8_] is offline
external usenet poster
 
Posts: 376
Default Pivot ungrouping on refresh?

Hi Jason

You should be using a dynamic range as your data source, and not
changing it each time you add more rows to the source data. If you
change the source, the PT will always cancel any existing grouping.

If you are using XL2003
Place your cursor within your source dataDataListCreatetick my List
has headers. Then make your source equal to this range, which Excel
recognises as a list and it will grow as you add more data.

If you are using XL2007
Place your cursor within your source dataInsert tabTableclick my
table has Headers.
It will create a Table name for you - Table1 by default - but you can
name it to whatever you want.
From the Design TabChange data sourcegive it the name of your Table.

For any version of XL
InsertNameDefine
Name myData
Refers to =$A$1:INDEX($A:$A,COUNTA($A:$A),INDEX($1:$1,COUNTA $1:$1))

Make the data source for your PT =myData

For more information on creating Dynamic ranges take a look at a
tutorial I wrote at
http://www.contextures.com/xlNames03.html

--
Regards
Roger Govier

Me! wrote:
Hi,

I have a pivot table where the ROW field is a date that is grouped by month
& year.

However, when I change the data source to include additional rows that I
have added to the underlying data & refresh the pivot, the grouping is
ungrouped, so my aggregagation by month becomes aggregation by date.

this is a huge problem as it adds hundreds of rows to my pivot table which
then runs over another pivot table.

Does anyone know how I can retain the row grouping on updating the data
source & refreshing.

Thanks,

Jason