I have an overview spreadsheet that shows all projects being worked on by my staff.
Each row in the overview sheet represents a single project.
Each row includes the project leader name and a difficulty rating of H, M, or L.
The data for each row in the overview spreadsheet is imported from individual project spreadsheets using formulas. The formula below imports a difficulty rating of H, M, or L.
=LEFT('\\IndividualSheets\[Proj123.xlsm]Worksheet'!$C$6,1)
I am trying to use a pivot table to track the number of H, M, and L difficulty projects carried by each employee.
The pivot table appears to set itself up correctly, listing the names of each employee and recognizing that the possible entries are H, M, and L. The attached image "Capture" shows the results of one project leader.

The problem I have is that it does not show correct project totals, it shows "0" for every entry. For example, instead of showing "15" H level projects for BGa, it shows "0".
If I manually enter an "L" into the overview sheet instead of a link to a cell in the individual project spreadsheet, that entry will show up in the pivot table totals. In the attached image, I manually entered 4 L level entries into the project, but there are several more H,M, and L ranked entries assigned to this employee that aren't included in the sum. I have tried using "Count" and "Sum" in the pivot table properties without success.

Capture2.PNG

Capture.PNG

Any suggestions?

Mr Bee