Forum Discussion
formula help please
It's better to keep summary by expenses in separate table or create PivotTable on general ledger to have such. Anyway, with such data layout you first need to map transaction descriptions in first column on column names, like here in right range
With that formula in F3 could be
=($C3+$D3)*IFNA((INDEX($K$3:$K$5,MATCH($B3,$J$3:$J$5,0))=F$2),0)and drag it to the right and down.
Even better to use Excel Tables to be more dynamic with data ranges.
SergeiBaklan I'm sorry I don't understand what you are trying to explain to me.
this is an exert from my spreadsheet
| transaction description | Debit/Credit | Balance | Account transfers | Stock & dog purchases | animal health | fuel | breaking & training | vehicle expenses | Vehicle payment | drawings | Bank charges | interest | |
| $ 229.10 | |||||||||||||
| AT | $ 50.00 | $ 279.10 | $ 50.00 | ||||||||||
| AT | $ 50.00 | $ 329.10 | $ 50.00 | ||||||||||
| AT | $ 50.00 | $ 379.10 | $ 50.00 | ||||||||||
| AT | $ 50.00 | $ 429.10 | $ 50.00 | ||||||||||
| INT | $ 0.12 | $ 429.22 | $ 0.12 | ||||||||||
| AT | $ 50.00 | $ 479.22 | $ 50.00 |
What I am wanting to do is have a formula that will automatically fill the Ledger columns with the figures from the debit credit column when I add the description abbreviation. ie: AT stands for account transfers so all the debit/credits that are an AT description will auto fill into the account transfers column, and the INT to interest column (rather than me having to manually copy and paste the figure into that column).
I hope this is clearer and allows you to help me with a formula to do this.
Thanks
First you need to explain to Excel that the transaction named AT shall go Account transfers column, INT to interest, etc. That's why we generate this helper range, otherwise how Excel know which transaction is belong to which column.
Having this range we may generate the formula which first finds in it column name which is belong to current transaction. Next, if above name is the same as current column name, we return Debit+Credit, into the cell for the current column and current transaction, otherwise 0.