Pivot Tables
 

I have 2 spreadsheets that contain some of the same fields. Spreadsheet A
shows what each persons monthly budget is. Spreadsheet B shows how many ads
each person has sold so it can be compared to budget. In a blank spreadsheet
(Spreadsheet C) I ran 2 pivot tables (1st- budget and 2nd-actual sales) to
compare next to each other. My problem is that if a person didn't sell
anything for the month of ex: July, then they are not included in the 2nd
pivot table. My goal is to have that person listed with a value of 0 so that
names are listed on the same line and can be visually compared easily. See
example below (Amy is perfect but Bill sold nothing in July so on Bill's line
there is Sue):
Publication ABC Magazine Publication ABC Magazine
Month Jul-09 Month Jul-09

Row Labels BUDGET Row Labels ADS SOLD
AMY $66,800 AMY $15,000
BILL $17,200 SUE $7,000
SUE $209,050 TONY $34,575
GERRY $167,050 WILL
RICK $137,050
TONY $176,050
ANNIE $17,200
WILL $273,450

Pivot Tables
 

Lisa wrote:
I have 2 spreadsheets that contain some of the same fields. Spreadsheet A
shows what each persons monthly budget is. Spreadsheet B shows how many ads
each person has sold so it can be compared to budget. In a blank spreadsheet
(Spreadsheet C) I ran 2 pivot tables (1st- budget and 2nd-actual sales) to
compare next to each other. My problem is that if a person didn't sell
anything for the month of ex: July, then they are not included in the 2nd
pivot table. My goal is to have that person listed with a value of 0 so that
names are listed on the same line and can be visually compared easily. See
example below (Amy is perfect but Bill sold nothing in July so on Bill's line
there is Sue):
Publication ABC Magazine Publication ABC Magazine
Month Jul-09 Month Jul-09

Row Labels BUDGET Row Labels ADS SOLD
AMY $66,800 AMY $15,000
BILL $17,200 SUE $7,000
SUE $209,050 TONY $34,575
GERRY $167,050 WILL
RICK $137,050
TONY $176,050
ANNIE $17,200
WILL $273,450


The sales pivot can't display the name unless that person is in the
data. One idea is to add rows with the missing names and zero/blank
sales. This is probably not the best idea though.

I would add the sales results to the budget table in sheet A and base
the pivot table on the amended data. That way everyone in the budget
will show up in the pivot. You can fetch the sales results into sheet A
using VLOOKUP, SUMIF, or some such.