Multiple Tables and Multiple Pivot Tables Help
 

I love tables and pivot tables, but I have a workbook that is driving me crazy. Here's the scenario:

Multiple tabbed workbook. There is one sheet with a table for variable data ranges. For example, one column is locations (like zip codes), another column is salesmen (their names), and another column is referral sources (e.g., customer referral, radio ad, etc.), the last is types of work done (e.g., painting, sanding, etc.). These are named ranges that are referenced elsewhere in the workbook for drop down data validation input. I use this workbook with multiple clients, so need to have the flexibility of changing the data constants (including quantity) without doing a search and replace on every sheet.

Next there are tabs for every month of the year, with a table in each to track a client name, the date a quote is given, the date a quote is accepted, who the salesman was, the referral source, the location, the amount of the quote, the amount accepted, what type of work is being quoted, etc. Pivot tables are used to show the break down of how many of each type of sale was made (i.e., one pivot table for locations; one pivot table for referral sources; one table for type of work; all these are filtered by salesman).

Everything up to this point is working spot on. Here's my issue: I want to be able to summarize all these monthly numbers into a year-in-review sheet. The year-in-review would ideally have each month summarized with all of the above data and then a grand total for each.

Here's what I've tried:

I can use GETPIVOTDATA, but I have to manually specify the individual item I want. Example: GETPIVOTDATA("# Bids Given",JAN!$P:$P,"Source","Client Referral") where "Client Referral" is one of those data constants from my data tab. I'd have to manual search and replace, and then if the client updates the data constants list, the summary page would be incomplete or inaccurate.

I attempted to consolidate multiple tables into one pivot table, but the data doesn't come across correctly for a variety of reasons.

My personal goal is to a) automate the column headers on the summary page using the data tab and then reference that column heading in the GETPIVOTDATA summary formula like the one referenced above OR b) accurately combine all the data dynamically from all the monthly tabs to be able to create pivot tables for the combined information.

Any suggestions?