I'm fairly new to pivot tables/charts. Any help would be greatly appreciated.

I maintain a simple spreadsheet in Excel 2007. I use it to track jobs. It lists the job number, customer name, location (20 geographic areas like Atlanta, Dallas, Nashville, etc...), schedule date and whether the job is complete or incomplete. If a job is complete I put a X in that column and leave incomplete blank (and vice versa). I created a simple (or so I thought) Pivot Table showing the areas and how many jobs are complete or incomplete. I couldn't figure out how to get a count using X under the complete or incomplete columns so I change it to a 1, so that problem was fixed.

The pivot chart was created with location under "Row Labels" and count of completed and incomplete under "Values". I also have Values under the "Column Labels" section. The chart comes out fine, and the table appears to be doing what I want it to do. My pivot table shows the locations in the first column, the count of completed jobs in the second and the count of incomplete in the third.

What I want is to be able to go to a specific location, double click on the number of completed (or incomplete) jobs and have it open up a separate worksheet showing those specific completed (or incomplete) jobs for that location. For example, I go to Dallas (in column #1) and see there are 8 completed jobs (in column #2) and 30 incomplete jobs (column #3) for this location. I want to be able to double click on the column showing the 8 completed jobs (#2) to see which specific jobs are completed. When I've done this on other spreadsheets it works perfectly. When I do it on my pivot table I get every job (38 total) for that location.



Anyone know what I'm doing wrong? I've done other pivot tables and not run into this problem, but as I mentioned, this all fairly new to me.

Try creating a single 'Status' column where you indicate complete, incomplete, not started, etc.

In your pivot table, use the Status column your column labels as well as the Values.

You'll wind up with the counts of the status in a column that corresponds to the statuses you're using. And double-clicking on the values will show the records you're looking to see.

HTH,
Charlie