Pivot Table no showing all data from source

[brianv]

Ok, Im not sure how best to explain this... so here goes...

We are rebuilding our time sheets, and using pivot table to summarize the data.

We have our projects, Job #, Labor Code, PW(Prevailing Wage), and then the summary of hours: Reg, OT, DBL, Other & Total...

There can be multiple Labor codes on a given job... and there can be some Labor codes with PW and the same labor code w/o PW.

So when we refresh the pivot table, the issue is that the pivot table will show a project, then breaks down into the labor code and PW, but if there is a second labor code w/o PW on the data source, then second labor code line item shown is blank, but the summary of hours shown. Meaning, ONLY the cell where the labor code should be shown is blank.

For Image Ex: Im not sure how to show the actual image... so here's a link to the image:
https://1drv.ms/u/s!ApYmeret_YzZnQaxdj2n6iyvz6Jk

The data source for cell D8 is Page1 cell I23 and that cell has 55-800 in it and J23 is "Yes" (PW cell)
The data source for cell D9 is Page1 cell I26 and that cell has 55-800 in it and J26 is blank (PW cell)

Why is cell D9 in the pivot table not showing 55-800, why is D9 blank....

Thanks
BV

 

[SpillerBD]

Double-Click on the .5 value to drill down on the source detail.

In the PivotTable Design options, or for that fields options, you will want to active the REPEAT ROW LABELS.

 

[brianv]

Geez... never occurred to me to look in field settings,,, i was looking all over options..... Thank you!

 

[brianv]

Is it possible to make a conditional format to compare the Grand Total on the pivot table to a specific sell on the data source. Essentially to ensure that when the data source is updated, that the pivot table is also refreshed.... considering the pivot table grand total cell can vary in location.....

 

[SpillerBD]

I thought so, but couldn't get desired results with a little testing.

I have the Pivot Table Refresh and Refresh All added to my QAT. I know that remembering to refresh is a problem for many and used to be for myself.

 

[brianv]

Ive got the PT to update when the file is opened, but yes, getting people to remember to refresh is an issue.
I could only get the formatting for work if the Pivot Table was increased in size, but if the table reduced, then the conditional format formula stayed where the Grand Total use to be....

 

[SpillerBD]

....then the conditional format formula stayed where the Grand Total use to be....

You can edit the conditional format and then choose if it relates to a specific value type in the Pivot Table which allows for the changing size of the Pivot Table.

You could have a single summary at the top of the sheet by way of another pivot table using the same PT Cache, or Cube Formula. You could also create a macro to activate the Refresh All or Refresh Pivot Table, assign that to a button on the sheet area with instructions for your users to click when the total indicates a variance.