Excel: Can I create a pivot table from two separate worksheets?


Hi, Say I have these sheets: Claims:

CaseId CustomerID ClaimNum ClaimLine Amount Applicable *
1 A 1 1 100 Y
1 A 1 2 100 N
1 A 1 3 100 Y
1 A 2 1 100 N
1 A 2 2 100 Y
2 B 1 1 200 Y
2 B 2 1 200 Y
2 B 2 2 200 N
Payments:
CaseId CustomerID Remitter Date Category1 Category2 Category3 Total
1 A John 15-Apr-11 $50 $50
1 A Mary 20-Apr-11 $50 $50
1 A Bob 30-Apr-11 $50 $50 $100
2 B Scott 01-Jul-13 $40 $20 $10 $70
Money: What I want in this sheet is a complex pivot table, or perhaps a complex VLOOKUP or array formula (???), with the data from these two separate worksheets. I'm not sure if I can do this in Excel or not? If I can do it in Excel, it would be cool as the end user gets immediate feedback/recalculations. Here is what I need:
CaseId CustomerNumber TotalAmount ApplicableAmount AgreedAmount ** WriteOff PaidAmount OutstandingAmount
1 A $500 $300 $200 $100 $200 $0
2 B $600 $400 $200 $200 $70 $130
Definitions: TotalAmount: The total of all claim lines in the Claims worksheet for a given CaseId ApplicableAmount: The total of all claim lines in Claims for a given CaseId where Applicable = "Y". * Note the Applicable column is hand coded based on an analyst's investigation. AgreedAmount: The amount that is agreed is owed based on legal discussions. ** Note that this column is hand coded by the analyst based on those legal discussions. WriteOff: The difference between the ApplicableAmount and the AgreedAmount. PaidAmount: The total of all payments for a given CaseId, where the Total column is the sum of payments in three different categories. OutstandingAmount: The difference between the AgreedAmount and the PaidAmount. I think (???) I could get close to this with a Pivot table for an individual worksheet. But, I'm unsure if a Pivot table can be created to combine the data from these two worksheets as shown above? If you KNOW this can't be done, can you let me know asap so I can start working on other alternatives? Which will be to export the Excel data to a database, run a summary program on the two separate tables, join the data, and re-export back into Excel. The downside is this will not give the end user immediate feedback, as this ETL process would run overnight. Also, the derivations would need to be done by the summary process, i.e. there could be no formulas in this worksheet, which would be a bummer. Thanks for any help you can provide. Much appreciated. Regards, Scott


This question generated 23 answers. To proceed to the answers, click here.

This thread is current as of January 06, 2014.


For more resources for Microsoft Excel