If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Pivot ungrouping on refresh?
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Pivot ungrouping on refresh?
Thanks Roger. I wasn't sure that a manual change of the data source
automatically cancelled any existing groupings. I'm fine with how to have my range dynamic - I just hadn't got round to changing this one report becauase it's only updated once every 6 weeks. I tend to use OFFSET and COUNTA. What benefit in using INDEX & COUNTA instead? Thanks again, Jason --- "Roger Govier" wrote in message ... 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 |
#4
|
|||
|
|||
Pivot ungrouping on refresh?
Hi Jason
Offset is a volatile function, and has to be recalculated every time there is a changed value, whereas Index isn't. I avoid Volatile functions "like the plague" - wherever I can. Index is a very fast and efficient function. -- Regards Roger Govier Me! wrote: Thanks Roger. I wasn't sure that a manual change of the data source automatically cancelled any existing groupings. I'm fine with how to have my range dynamic - I just hadn't got round to changing this one report becauase it's only updated once every 6 weeks. I tend to use OFFSET and COUNTA. What benefit in using INDEX & COUNTA instead? Thanks again, Jason --- "Roger Govier" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|