Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Me! Me! is offline
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
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


  #3   Report Post  
Posted to microsoft.public.excel.misc
Me! Me! is offline
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 376
Default 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



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

On Wednesday, March 24, 2010 at 5:58:06 AM UTC-4, 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


Hi Jason

I realize you have posted this back in 2010, but I do nto see anything else more recent.

I am having the same problem and Jason's instructions to correct the issue has not helped. My table/range includes the file name and the complete data range of the table, yet, the dates are still ungrouping when refreshed. Can you advise how you corrected this aggravating problem?


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

Il giorno mercoledì 24 marzo 2010 10:58:06 UTC+1, Me! ha scritto:
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


Hi Jason!

how did u manage to fix it? I do have the same problem (excel 2013) but I cannot get the solution from Roger. I cannot insert a table if a pivot is already existing! Am I missing something?

Many thanks in advance.

Andrea
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

On Friday, July 10, 2015 at 7:37:08 AM UTC-5, wrote:
Il giorno mercoledì 24 marzo 2010 10:58:06 UTC+1, Me! ha scritto:
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


Hi Jason!

how did u manage to fix it? I do have the same problem (excel 2013) but I cannot get the solution from Roger. I cannot insert a table if a pivot is already existing! Am I missing something?

Many thanks in advance.

Andrea


I know this is old but I just fixed this issue. The reason the Date fields ungroup is because the new data contains invalid dates. Insert a =ISTEXT function in an unused column to see which dates need to be re-entered. Once the dates are true date format, refresh and grouping should be allowed again.
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

On Friday, 9 December 2016 17:50:34 UTC, wrote:
On Friday, July 10, 2015 at 7:37:08 AM UTC-5, wrote:
Il giorno mercoledì 24 marzo 2010 10:58:06 UTC+1, Me! ha scritto:
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


Hi Jason!

how did u manage to fix it? I do have the same problem (excel 2013) but I cannot get the solution from Roger. I cannot insert a table if a pivot is already existing! Am I missing something?

Many thanks in advance.

Andrea


I know this is old but I just fixed this issue. The reason the Date fields ungroup is because the new data contains invalid dates. Insert a =ISTEXT function in an unused column to see which dates need to be re-entered. Once the dates are true date format, refresh and grouping should be allowed again.


This worked for me, my date was in the wrong format
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

On Friday, December 9, 2016 at 12:50:34 PM UTC-5, wrote:
On Friday, July 10, 2015 at 7:37:08 AM UTC-5, wrote:
Il giorno mercoledì 24 marzo 2010 10:58:06 UTC+1, Me! ha scritto:
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


Hi Jason!

how did u manage to fix it? I do have the same problem (excel 2013) but I cannot get the solution from Roger. I cannot insert a table if a pivot is already existing! Am I missing something?

Many thanks in advance.

Andrea


I know this is old but I just fixed this issue. The reason the Date fields ungroup is because the new data contains invalid dates. Insert a =ISTEXT function in an unused column to see which dates need to be re-entered. Once the dates are true date format, refresh and grouping should be allowed again.


I know this is old but thank you it worked!!
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Pivot ungrouping on refresh?

On Friday, December 21, 2018 at 7:37:15 AM UTC-8, wrote:
On Friday, December 9, 2016 at 12:50:34 PM UTC-5, wrote:
On Friday, July 10, 2015 at 7:37:08 AM UTC-5, wrote:
Il giorno mercoledì 24 marzo 2010 10:58:06 UTC+1, Me! ha scritto:
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

Hi Jason!

how did u manage to fix it? I do have the same problem (excel 2013) but I cannot get the solution from Roger. I cannot insert a table if a pivot is already existing! Am I missing something?

Many thanks in advance.

Andrea


I know this is old but I just fixed this issue. The reason the Date fields ungroup is because the new data contains invalid dates. Insert a =ISTEXT function in an unused column to see which dates need to be re-entered. Once the dates are true date format, refresh and grouping should be allowed again.

I know this is old but thank you it worked!

Fixing the date format worked for me too!! woohoo for the solution!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
slow document / pivot table refresh and pivot function Justin Larson[_2_] Excel Discussion (Misc queries) 1 April 2nd 09 06:41 PM
Ungrouping the fields inside pivot table(Excel 2007) arun Excel Discussion (Misc queries) 1 March 26th 09 01:47 PM
Create refresh button in worksheet to refresh Pivot Table Data Ron Excel Worksheet Functions 1 October 13th 07 01:20 AM
refresh a new worsheet on pivot table refresh [email protected] Excel Worksheet Functions 0 February 9th 07 08:39 PM
pivot refresh clears pivot fields? Leejo Excel Discussion (Misc queries) 0 October 13th 05 03:10 PM


All times are GMT +1. The time now is 11:18 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"