Problems with grouping in pivot table

Posted by d on January 10, 2001 1:03 AM

Hi,

I have a column of data in a pivot table which contains dates, and am trying to group it by year, month etc.. however everytime I right click to group and choose group from the menu I get the error cannot group this selection! any ideas, I have tried everything I can think of, I have another similar worksheet and it works fine there, am I missing something obvious, any help appreciated

Posted by Dave Hawley on January 10, 2001 3:40 AM

Hi d

I'm betting your column or row the Pivot Table is reading the dates from either contains an invalid date or blanks. Either way Excel will not be able to group your date field.

I use Pivot Tables a lot and I find my data the the Pivot Table reads from is always expanding, so I use Dynamic named ranges as my Pivot Table source. This is because selecting a range that extends past the last entry (blank row/columns) in your data, causes the grouping problem.

There are a few examples of Dynamic ranges on my web page: http://www.ozgrid.com under the link "Dynamic ranges"

To make a dynamic range that would expand down rows and across columns (a table) you would simply change the last argument in the formula from 1 to 5. This would then expand across 5 columns. Anyway check out the web page and any problems let me know.
OzGrid Business Applications

Posted by d on January 10, 2001 6:54 AM

Many thanks Dave, yes I that is exactly what was causing my probs !!