» Getting Drill-Down Details

I am having some problems with the dates in my pivot tables, and I was hoping if someone new whether it is a limitation of Excel or not.
I have various groupings, but it is pretty straightforward what I want to accomplish.
I have a set of customers and I am trying to total Year To Date sales values i.e

Year Month Account No. Customer Name Value Kilos
------------------------------------------------------------------
2003 Jan AAA123 Bob 100 20
Feb ABC234 Frank 250 20
etc

However, I have 18 months worth of sales history in my Access database. Each customer purchases every month and there are around 10 customers in total in this particular scenario. I want my pivot table to allow me to define the range of months to display, i.e. Jan - Jul, but to total by customer for that period, and not show the month by month breakdown. The reason is that I want to try and build a comparison for the previous year, and this is where things go wrong. I get Dr. Watson application error messages appearing on my screen then excel gets killed.
Any one able to shine some light on the situation?

Hi Gareth,

First question: Are the dates (you mention only months above) actually 'dates' as in date values in Excel, or are they text values ("Jan","Feb") etc?

Either way you should be able to filter to only show those you want using the pivot table filters that you seem to be applying already.

You should not be getting those error messages.

My first inclination is to try two things:

1) Try to do the same thing with a clean, small data set (perhaps copy and paste as values to a new workbook).

Then re-create the pivot table with that clean small data set, and see what happens.

If all is well, it would lead me to think that either your computer is low on memory, or the original data set is somehow corrupted and needs to be verified as a separate exercise (probably in Access?)

2) Re-install Excel.

It could be that something in Excel that only gets used by pivot tables has been corrupted, and is causing those errors.

If neither of those shed any light, post back and we can explore further.

Alan.

I have a pivot table with a date field that I want to group by quarter, month, year, etc. Everytime I click on group, I get an error box that says "Cannot group this section." Any ideas? Please advise.

Thanks.

Hi Eric,

Where are you clicking on 'group'?

I cannot see exactly what you mean.

Alan.

Eric,

Make sure (if you have not done so already), that when you right click to bring up the grouping option, that you have the Date field highlighted first.