Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Distributing Pivot Tables where pivot table data can't be accessed | Excel Discussion (Misc queries) | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) |