Well, take a look at this. In this example, I have combined the data into a single sheet called Log. The data table itself is called CaseLog (I hate spaces). I added a field for Investigator and another for Review date.
Pivot1 shows the count for each Investigator for each Incident type by Jurisdiction. Any particular investigator's records can be collapsed, but the totals remain the same. Alternatively (see Pivot2), we could add an investigator filter and the totals would update for the filter applied.
I also added drop down data validations for Incident and Investigator. I noticed a few entries for jurisdiction that had extraneous spaces or multiple spellings and that will screw up the pivot results. Data validation will prevent that.
Anyhoo...that's my first cut at it. If it looks promising, we can forge ahead. If not, back to the drawing board. This is just for illustrative purposes only. You can play around with the pivots and get a feel for how they work. I resisted for a long time but use them a lot now. They are easy as all get out.
|