Forum Discussion

Elva_Tanguerre's avatar
Elva_Tanguerre
Copper Contributor
Sep 05, 2023

Match organisations with transactions (Building a personal finance report)

Hello, I am trying to keep track of someone's finance. Issue: she deals with multiple banks, and for each bank, has several accounts, and each account has different attributes. I want to run reports on these attributes, over years (how much in liquidity vs stock, how much per bank, etc.). I can create the accounts on one tab (account, bank and attributes) and amount updates on another tab (account, amount, date) but I am stumped when it comes to reports as the info is on two tabs. At the same time, I would like to avoid having to enter the full bank info for every transaction row. Other than multiple vlookup and pivot reports, I am stumped.  

  • You can solve this in several ways. If you are thinking of using a pivot table, insert one but check the "use data model" box. As soon as you do that, you will be able to use fields from more than one table in the pivot table. You will have to set the relation between both tables (Excel will ask you to do so as soon as you drag a field form a second table onto your pivottable).
  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    You can solve this in several ways. If you are thinking of using a pivot table, insert one but check the "use data model" box. As soon as you do that, you will be able to use fields from more than one table in the pivot table. You will have to set the relation between both tables (Excel will ask you to do so as soon as you drag a field form a second table onto your pivottable).

Resources