Excel 2010 Pivot Table Column Lables & Report Filter Help

[Carin]

I have two pivot tables that feed off of the same data.

Pivot1: I have four column labels: 1, 2, 3, 4

Pivot source data column E has either a 1, 2, 3, or 4

My Row Label are cities in the area - so the pivot will list the city and tell you many of 1, 2, 3, or 4 there are in the source data.

Problem - if my source data does not contain one of the choices: let's say 2, then the Column labes read 1, 3, then 4. Everything seemed reasonable until the next month's report had a 2 in it. My Column Label still read 1, 3, and 4. I have to select the Count of City Name dropdown and check the number 2. Then my Column Lables are sorted like this: 1, 3, 4, then 2.

Is there a solution to this?

Pivot2: I have a pivot table for each number listed above: 1, 2, 3, and 4. Pivot1 just gave a count per city and Pivot2 actually shows the data that is being counted. When 2 was missing from the data, my pivot table that is filtered to only show 2 now shows ALL. Once I received my new monthly report, I had to select the report filter dropdown to select 2.

Is there a solution to this?

Thank you

 

[Carin]

Well, figured out a workaround and thought I would post in case someone came across my post and needed some kind of a fix. Since I could not figure out how to manipulate the pivot table itself, I wrote a SIMPLE macro to add a 1, 2, 3, and 4 at the end of the data source.

Range("e1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "1"
Range("e1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "2"
Range("e1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "3"
Range("e1").End(xlDown).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "4"

Now when there is no data containing any one of the above numbers, the labels and filters stay in place because there is a blank record containing either the 1, 2, 3, or 4.

 

[XL&ME]

Not sure if this will solve your problem, but it maybe worth a try. Right click on the label and select field settings. Then click on the layout and print tab. Then click the check box beside "Show items with no data".

 

[Carin]

Thank you so much for responding. I did try that. When I first saw that checkbox I was so excited only to find out it didn't do anything for this situation which made me wonder what it is used for??
Again, thank you.

 

[XL&ME]

Thanks for the feedback. Not sure why it did not work in your specific situation. I have used this method many times.