![]() |
#3
|
|||
|
|||
![]()
1. You need a sheet Transactions with a table for imported data (Columns e.g. Organization, TransactionDate, SumIn, SumOut, optionally a calculated column TansactionYear with formula "= YEAR([@TransactionDate]"). Best will be to define this as a Table (e.g. tTransactions);
2. You need a sheet Organizations, optionally with field in 1st row where user can enter year number. When table is used for several years, and there will be a cell for year number, define this cell as Name, e.g. nRepYear. 3. On sheet Organizations, you need a table with columns Organization, Saldo (column headers must be not higher that row 3). And again, the best will be to define this as Table (e.g. tOrganizations) 4. In Table tOrganizations, fill the column Organization with list of all organizations from Table tTransactions; 5. Into 1st data row of Table tOrganizations, into column Saldo enter the formula: Code:
=SUMIFS(tTransactions[SumIn],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear)-SUMIFS(tTransactions[SumOut],tTransacions[Organization],[@Organization],tTransacions[TransactionYear],nRepYear) Current formula assumes, that in tTransactions you have separate columns for incoming and outgoing transactions, and sums in those columns are positive. Otherwise you have to make corrections in formula. In table tOrganizations, in column Saldo is displayed current saldo for every organization (for report year determined by nRepYear). |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
![]() |
DiegoManBR | Word | 2 | 04-14-2016 09:33 AM |
![]() |
EnricoHorst | Excel | 3 | 04-05-2016 12:45 AM |
How to find number of coma and then add that number of rows in word using macro? | PRA007 | Word VBA | 7 | 05-27-2015 10:45 PM |
Wrong page number 2013 | officejunkie | Word | 2 | 05-06-2015 12:57 PM |
One level 3 heading number is wrong - but all others are fine | Dr Wu | Word | 4 | 05-09-2013 10:07 AM |