Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary table from two worksheets
Hi,
I need to create a summary table from two worksheets. They are forecast budget and actual spending. Project ID and Category are constantly updated when I get those data so that I would like to use VBA. First worksheet has Actual data as follow. Project Category Quarter Amount 38852 Consulting Q2'09 2000 38852 Payroll Q1'09 1000 38852 Payroll Q2'09 4000 38852 Payroll Q3'09 2000 38852 Software Q2'09 9500 39810 Consulting Q2'09 6000 39810 Office Expenses Q2'09 500 39810 Office Expenses Q3'09 500 39810 Office Expenses Q4'09 500 Second worksheet has Forecast data as follow. Project Category Q2'09 Q3'09 Q4'09 38852 Boards 0 0 1000 38852 Consulting 3000 0 0 38852 Payroll 3000 3000 3000 39810 Consulting 2000 2000 2000 39810 Software 1000 1000 1000 Then I would like to create a summary data as follow. Project Category Q1'09 Q1'09 Q1'09 Q2'09 Q2'09 Q2'09 Q3'09 Q3'09 03'09.... FCast Actual Diff FCast Actual Diff FCast Actual Diff .... 38852 Boards 0 0 0 0 1000 -10000 0 0 0 38852 Consulting 0 3000 -3000 2000 0 2000 0 0 0 38852 Payroll 0 1000 -1000 3000 4000 -1000 3000 2000 1000 38852 Software 0 0 0 0 9500 -9500 0 0 0 39810 Consulting 0 0 0 2000 6000 -4000 2000 0 2000 39810 Software 0 0 0 1000 0 1000 1000 0 1000 39810 Office 0 0 0 0 500 -500 0 500 -500 Expenses How can I create this table? Any help greatly appreciated. Thanks, Jen |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summary table from two worksheets
if you add a column for say "Type" which would be either "Actual" or
"Forecast", then you and simply append one table to the other and apply/create a pivot table. "Jen" wrote in message ... Hi, I need to create a summary table from two worksheets. They are forecast budget and actual spending. Project ID and Category are constantly updated when I get those data so that I would like to use VBA. First worksheet has Actual data as follow. Project Category Quarter Amount 38852 Consulting Q2'09 2000 38852 Payroll Q1'09 1000 38852 Payroll Q2'09 4000 38852 Payroll Q3'09 2000 38852 Software Q2'09 9500 39810 Consulting Q2'09 6000 39810 Office Expenses Q2'09 500 39810 Office Expenses Q3'09 500 39810 Office Expenses Q4'09 500 Second worksheet has Forecast data as follow. Project Category Q2'09 Q3'09 Q4'09 38852 Boards 0 0 1000 38852 Consulting 3000 0 0 38852 Payroll 3000 3000 3000 39810 Consulting 2000 2000 2000 39810 Software 1000 1000 1000 Then I would like to create a summary data as follow. Project Category Q1'09 Q1'09 Q1'09 Q2'09 Q2'09 Q2'09 Q3'09 Q3'09 03'09.... FCast Actual Diff FCast Actual Diff FCast Actual Diff .... 38852 Boards 0 0 0 0 1000 -10000 0 0 0 38852 Consulting 0 3000 -3000 2000 0 2000 0 0 0 38852 Payroll 0 1000 -1000 3000 4000 -1000 3000 2000 1000 38852 Software 0 0 0 0 9500 -9500 0 0 0 39810 Consulting 0 0 0 2000 6000 -4000 2000 0 2000 39810 Software 0 0 0 1000 0 1000 1000 0 1000 39810 Office 0 0 0 0 500 -500 0 500 -500 Expenses How can I create this table? Any help greatly appreciated. Thanks, Jen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating summary table of input from other worksheets | Excel Worksheet Functions | |||
Summary of worksheets | Excel Discussion (Misc queries) | |||
Creating summary table from detail table | Excel Worksheet Functions | |||
PIVOT TABLE - Summary Table into a Databasae Table. | Excel Worksheet Functions | |||
Summary of all worksheets | Excel Programming |