I have a table that contains daily disbursement transactions complete with accounting journal entries, from which reports and Journals are generated. How do I transfer the data from this list into the Check Disbursement Journal? I used the SUMIFS formula but what is the formula to automatically return the account titles under the sundry column when I manually enter the check numbers? I have attached the sample data table and Journal.

Attached Files

CKDJ.xlsx (153.6 KB, 12 views)

I dabbled with your workbook a bit, but I didn't cope at report, as I couldn't find any rules how the report is composed.

I added some registry Tables (tAccounts, tAccMajor, tPayees, tResponsibilities), and defined some names to use in data validation lists (to exclude user typos at data entry). The names used as data validation list sources are preceeded with "l" (like lAccounts).

There was a lot of information repeated for every check in Table1 (Transactions), so I added a table tChecks, where this info is entered once, and in transactions table this info is returned by formula. And for checks in transactions table data validation list is defined, so user has to define the check first.

All columns in tables containing formulas are colored.

Instead of simple month number in transactions table, I calculated the month number in format yyyymm. This allows to have in table transactions from different years, whenever you need it.

In transactions table, and in table tChacks, are a couple of calculated columns like table row number, and row number of entry from month where the date entered into report header on sheet "CHDJ-GF 101" belongs to. The idea is, you can use them to get right values (using INDEX function with row number associated with report).

At end of table tChecks are columns, where totals for debet and credit sums by major amounts for every check are calculated. Currently you can have up to 15 major accounts. Probably these totals are what you need in your report, but I couldn't figure out which values to where must go.

Update. Into field A8 on report sheet I entered the formula which returns 1st check for report month, then 2nd, etc. After that I found out, that some checks may have several rows in report, and discarded the formula, but it remained in one cell. Until then I hoped to edit the report in a way, that user enters data into report header, and the report for this month is generated.

Attached Files

CKDJ.xlsx (129.5 KB, 14 views)

Thank you AvriLaanemets. The transactions table looks neater than mine. I will try using your tables by adding data. I hope you could make the report easier also. The report (CHDJ-GF 101) is a required format by the Commission on Audit. My problem in the making of the report is that I must define the account code in the sundry account column first in order to return the corresponding amounts of that account code from the transactions. Check number 623827 contains the ff accounting entry
Account Code
1-03-05-030-P11,000.00(Debit)
1-01-02-010-P11,000.00(Credit)
1-01-01-010-P2,307.61(Debit)
5-02-16-010-P2,779.06(Debit)
5-02-16-030-P5,913.33(Debit)
1-03-05-030-P11,000.00(Credit)
When I enter check number 623827 in the report, the amount of 1-01-02-010 (E7) automatically appears through the sumifs formula. What is the formula that returns all the other accounts not found in Row 7 under the debit sundry and credit sundry columns. The rule is all accounts used that are not found in Row 7 should go under the sundry columns. Take note that the sundry columns have separate debit and credit account code and amounts. I hope I am making sense.
I like your idea of just entering a data into the report header resulting in a report formatted in a Check Disbursement Journal.

What are sundry accounts? In transactions table are debit and credit amounts only!

I think the way to go is to calculate all fields needed for report in checks table - all in single row. A possible schema will be:

1. You need to estimate the number of needed report rows for every check. And you have to decide the max number of check rows in report;
2. In checks table instead columns for major accounts separately for debit and credit, you must have also group(s) for sundry accounts. And you have every of those accounts for report row numbers from 1 to max report row. Also you must have an additional 1-row Table at top of those calculated columns, where the according report row number is stored (like the Table where according accounts are currently stored);
3. In report, you must have some hidden columns, where
a) the number of check is stored (from 1 to max number or checks in report month on checks sheet), repeated for number of report rows in checks table (like 1, 2, 2, 3, 4, 4, 4, ...);
b) the number of check row is stored (from 1 to number of check rows in checks table).

From there on, you read info from proper columns on check table into report (you must have enough rows prepred for report - before printing you can use autofilter to hide empty rows.

Sundry accounts are all accounts not defined in Row 7 of the report. I'm sorry the columns of the report are confusing. The debit column of the report consist of pre-defined accounts 1-01-02-010, 2-02-01-010 and sundry account. For example, account 2-99-99-990 of check 623828 goes to sundry account column because it's not 1-01-02-010 or 2-02-01-010. In your suggestion, where in the tchecks will I enter the maximum number of rows for each check?
Also, why are the payees of checks 623825 and onwards not returned in the report?

It assumes they are defined in report! To get the report automatically, they MUST be defined for check, or for transaction.

The number of row in additional table at calculated columns on checks sheet (and the max number of rows) must be calculated. It assumes there must be strict rules which accounts are going into which column of report, and maybe you also need some additional info saved into major accounts table to do those calculations.

I haven't Office available at weekend, so currently I can't check.

Some additional thoughts:
Keep all entry columns in tables in left side columns, and all calculated columns at right of them and colored - the possibility that user enters something into field with formula will be less. It also makes easier to copy entered data from table, or to copy data into table (when user must to add/overwrite data in table with data copied from somewhere, the range must contain only entry columns, and instead of "Copy", "PasteSpecial>Values must be used);
After you get your application working properly, hide all sheets/calculated columns not needed for user to be seen (like Table row numbers). Abundant info will only confuse the user;
For designing time, you can have columns where you distribute amounts to major accounts and rows at right of checks table. As final step, create an additional sheet, which will be hidden, and where you have a table of checks for report month read from checks table and listed - all columns where you distribute amounts to major accounts and to report rows mus be in this Table instead of checks Table. The Table must have enough prepared rows for any report month (estimate max number of checks in month, multiply this number e.g. with 2, and you have a number of rows you probably need). As result, when you use this workbook for a year, you have at least about four times less of cells with formulas as when having them all in checks table, which results in less calculation time. When you use the workbook for several years, the gain in calculation time will grow.

Some check numbers in report were in numeric format. In checks sheet, the check numbers are text (what is logical, as some check numbers are definitely not numeric).

In Excel "1234" and 1234 are different values!

Thank you for all the tips, I admit I don't follow some of them but I will try until I got them working correctly.

Could we go back to this problem? As you suggested, I inserted column V in table 1 indicating if the major account is Sundry or Column. I have also simplified the location of Sundry columns in the CHDJ sheet. What's the formula for putting the sundry major accounts of each check in the CHDJ? Please help me find peace about this. I've tried several formulas but with unsatisfactory result. Problem areas were painted yellow.

Attached Files

CKDJ (2).xlsx (79.4 KB, 9 views)