C
Catinalana
I love tables and pivot tables, but I have a workbook that is driving m
crazy. Here's the scenario:
Multiple tabbed workbook. There is one sheet with a table for variabl
data ranges. For example, one column is locations (like zip codes)
another column is salesmen (their names), and another column is referra
sources (e.g., customer referral, radio ad, etc.), the last is types o
work done (e.g., painting, sanding, etc.). These are named ranges tha
are referenced elsewhere in the workbook for drop down data validatio
input. I use this workbook with multiple clients, so need to have th
flexibility of changing the data constants (including quantity) withou
doing a search and replace on every sheet.
Next there are tabs for every month of the year, with a table in each t
track a client name, the date a quote is given, the date a quote i
accepted, who the salesman was, the referral source, the location, th
amount of the quote, the amount accepted, what type of work is bein
quoted, etc. Pivot tables are used to show the break down of how many o
each type of sale was made (i.e., one pivot table for locations; on
pivot table for referral sources; one table for type of work; all thes
are filtered by salesman).
Everything up to this point is working spot on. Here's my issue: I wan
to be able to summarize all these monthly numbers into a year-in-revie
sheet. The year-in-review would ideally have each month summarized wit
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 individua
item I want. Example: GETPIVOTDATA("# Bid
Given",JAN!$P:$P,"Source","Client Referral") where "Client Referral" i
one of those data constants from my data tab. I'd have to manual searc
and replace, and then if the client updates the data constants list, th
summary page would be incomplete or inaccurate.
I attempted to consolidate multiple tables into one pivot table, but th
data doesn't come across correctly for a variety of reasons.
My personal goal is to a) automate the column headers on the summar
page using the data tab and then reference that column heading in th
GETPIVOTDATA summary formula like the one referenced above OR b
accurately combine all the data dynamically from all the monthly tabs t
be able to create pivot tables for the combined information.
Any suggestions
crazy. Here's the scenario:
Multiple tabbed workbook. There is one sheet with a table for variabl
data ranges. For example, one column is locations (like zip codes)
another column is salesmen (their names), and another column is referra
sources (e.g., customer referral, radio ad, etc.), the last is types o
work done (e.g., painting, sanding, etc.). These are named ranges tha
are referenced elsewhere in the workbook for drop down data validatio
input. I use this workbook with multiple clients, so need to have th
flexibility of changing the data constants (including quantity) withou
doing a search and replace on every sheet.
Next there are tabs for every month of the year, with a table in each t
track a client name, the date a quote is given, the date a quote i
accepted, who the salesman was, the referral source, the location, th
amount of the quote, the amount accepted, what type of work is bein
quoted, etc. Pivot tables are used to show the break down of how many o
each type of sale was made (i.e., one pivot table for locations; on
pivot table for referral sources; one table for type of work; all thes
are filtered by salesman).
Everything up to this point is working spot on. Here's my issue: I wan
to be able to summarize all these monthly numbers into a year-in-revie
sheet. The year-in-review would ideally have each month summarized wit
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 individua
item I want. Example: GETPIVOTDATA("# Bid
Given",JAN!$P:$P,"Source","Client Referral") where "Client Referral" i
one of those data constants from my data tab. I'd have to manual searc
and replace, and then if the client updates the data constants list, th
summary page would be incomplete or inaccurate.
I attempted to consolidate multiple tables into one pivot table, but th
data doesn't come across correctly for a variety of reasons.
My personal goal is to a) automate the column headers on the summar
page using the data tab and then reference that column heading in th
GETPIVOTDATA summary formula like the one referenced above OR b
accurately combine all the data dynamically from all the monthly tabs t
be able to create pivot tables for the combined information.
Any suggestions