Log In
Register
Home
September 14, 2020
Offline
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.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
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.
Moderators
January 31, 2022
Offline
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.
1 Guest(s)
