View Single Post
 
Old 05-09-2021, 11:25 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 636
ArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of lightArviLaanemets is a glorious beacon of light
Default

I'd scrap your current workbook, and create something like in added example (I designed a minimum of it, simply to give you some ideas). The idea is all data entry (except various registers) are on single worksheet, and what you have currently for data entry (a sheet for every year) are replaced with a single report sheet (+ another report sheet you are asking help for).

For RepSummary report, you set data validation lists to select start and end years, and a summary table for any number of years is displayed.

All info which is calculated based on another data (like Conversion, Income, Payment Processing Expenses, etc.), are calculated only in reports.

The design is easiest, when on data entry sheet real values are entered for every month for every account. When you want to keep the practice, where an amount is entered for some month, and afterwards next entry is calculated based on previous month value (as is in your example), the design will get more complicated (e.g. you need an additional registry for percentages per account an year, have to enter rows with empty amounts for calculated months, and have additional amount column, where entered amount is read in in case such exists, or the amount is calculated based on previous data.

This kind of design ensures, you design the app once, ant you can practically forget it. I have some apps made nearly 20 years ago, which are in use yet, and there was no need for me to edit them.
Attached Files
File Type: xlsx AccountingExample.xlsx (22.6 KB, 3 views)
Reply With Quote