Hi,
I have a file of Accounting which have two Sheets named; "General Journal" and "General Ledger".
I entered data in "General Journal" and I want to post data from Journal to Ledger by Using Formula.
I have posted the name of the account by using a formula but the amount of debt and credit is not being posted.
Please see the file attached and give me a formula for the Ledger posting.
Thanks.
Hi Reehan
In a formula, the @ operator refers to the current row, that means The account used is not always what you expect, see the image, it is looking for [Machine Shifting Expense to another Hall] instead of Cash.
I believe I fixed the Debit/Credit issue for the General Ledger sheet, but to get the corresponding narratives in place is a different issue. One I haven't figured out yet.
That said, I believe that the structure of the Journal table is not suitable for making summaries. The fact that you used 12 helper columns (hidden, and I added a few more) indicates that you went through many extra steps to allow for the table to look like it does. A visually appealing Journal table as the basis for a summary at a Date/Account level.
That's asking for trouble. Better to use a simple tabular structure for the raw journal data, where every row has an ID, a date, an account number, a narrative and a single amount (debit = positive, credit = negative). Creating separate Journal and General Ledger reports from there would be relatively easy.
I guess Power Query could be used to re-shape the Journal data as you have it know, but I haven't gone that far.
