I don't understand what you mean by "Turnover," so I don't know how to calculate it.
When you say I think you mean that you want the annual "Turnover" for the previous 5 calendar years for each client, at least back to their commencement date.
IOW,
- For a client that started 6 months ago, their will be no "Turnover" value.
- For a client that started 2.999 years ago, there will be two years "Turnover" values,
- And for that client that started 6 years ago, there will be five years "Turnover" values.
Personally, in the interests of consistency and completeness, I would add one more "Turnover" column, to wit; "Turnover To Date, This Period."
To reiterate, The "Turnover" periods for each client are based on that clients Commencement Date
The following is based on this statement
Report Table Columns
- Commencement Date, especially Month and Day
- Client Name
- Blank
- TurnOver 1
- TurnOver 2
- TurnOver 3
- TurnOver 4
- TurnOver 5
- TurnOver To Date, This Period
As a new project using SQL directly from the Database, you would first code to retrieve the previous 5+ years of data. If you stop coding at this point, you merely have to run the code once a month to completely rebuild the table. You can also run the code intermittently to bring it up to date on any given day. This would remove almost all User activity from the workbook. The user would merely click a button or run the macro from the menu. It could otherwise be run on Workbook Open as mentioned above.
If rebuilding the report took too long, the code's parameters could be changed to only retrieving the current periods data. But more code would have to be added to maintain the "turnover" columns.
If you merely want to automate the current project, I would move the monthly data columns to a separate sheet in the interest of readability of the report. This sheet would be automatically updated from the monthly autogenerated workbook. The code could
only be run once a month and must have checks built in to insure that it fails if this month's data has already been retrieved.
Data Table Columns
- Client Name
- Blank
- 60
- 59
- 58
- ...
- 1
The code would first delete the data cells, (Shift Left,) in column named 60. Then it would import the current month's data into column named 1. The code must verify that the imported data is in the proper client's Row.
The six "Turnover" formulas on the Report sheet might be
- =SUM(DataSheet!$C2:$N2) = Year1
- =SUM(DataSheet!$O2:$Z2) = Year2
- =SUM(DataSheet!$AA2:$AL2) = Year3
- =SUM(DataSheet!$AM2:$AX2) = Year4
- =SUM(DataSheet!$AZ2:$BJ2) = Year5
- =SUM(DataSheet!$BL2:$BV2) = Period To Date
Note that each formula is copied down to the Row of the bottom of the client list.
Note that each formula Sums 12 months' data.
Note that I may be misunderstanding how to calculate "Turnover."
It is up to you to decide which method you want to use.
I prefer the SQL Direct From Database method because the code is easier to maintain, being all in one block. That it needs no references to anything other than the database, that it can automatically update the Report when clients are added or changed.
I believe that the Report Sheet + DataSheet + autogenerated update sheet + Formulas is easier for a beginner to understand. Such understanding is required to maintain the system. However, this method is subject to "Feature Creep" which can necessitate a compete overhaul and/or replacement in the unseen future.