Forum Discussion
How do I pull a sum from two tables into a pivot table?
Hello all,
I am developing a user-friendly personal finance model via excel where expenses can be recorded on multiple cards (credit and debit.
We all have multiple credit and debit cards, but what if you want to know how much your expenses are from all cards cumulatively? Say you paid for gas on the credit card last week and then again on the debit card this week. How do I reflect this data in a pivot table when I record credit card activity in one table and debit card activity in another table? (Refer to https://1drv.ms/x/s!Ao0ehPCQebZznGADUvgFBnFCKEc4?e=tVHMedto work this out. Both tables are identical, but the end goal should be possible to come regardless of this coincidence.)
I really appreciate any and all answers.
3 Replies
- Thank you for the response!
How exactly did you do this?Hi Amrick615
- Create a Connection on each Table (method 1 in https://youtu.be/sb0hmwiFM-E)
- Append both query connections (same video) and when Loading To... select Pivot Table Report (easiest way in your case):
Look at the workbook I shared > Go to Excel Data tab > Queries & Connections (the corresponding pane opens on the right) - 3 queries:
1. Connection to your Table CreditCard
2. Connection to your Table DebitCard
3. CombinedCards is the one where the above 2 are Appended
Hope this helps
