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.
|