An example how I'd do something like this!
All game results are entered on single sheet (Scores).
I used ISO week definition, and it looks like this is what you used too mostly. But it looks like you messed up this sometimes - p.e. sheets 08_01, and 09_01, where the week start is Sunday! Are tables there starting at 08.0.2024 and 15.01.2024 (both Sundays), or 09.01.2024 and 16.01.2024 (both Mondays, as the row header in table says)?
There are some registry sheets (Players, Rounds, Calendar, Lists), which are used as sources for Data Validation Lists, and are needed when creating various report tables or calculations.
On List sheet are defined the period lists for selecting periods for various reports. Based on those lists, dynamic ranges are defined, which exclude periods later then current one.
On Scores sheet, the table contains some calculated columns, which are used in report calculations. You can hide them, and better do this so you don't accidentally mess them up!
I used Defined Tables here, as I think they have some useful advantages (all formulas and formats are expanded to new rows automatically, unless you have several different formulas or formats in same column, and the formulas are easier to read). But there are also some limitations, and one of them is, that there can't be several columns with same header (this is the reason why some column headers in my report are different from ones you used in your tables).
I created a single example of report sheet, based on your design of weekly tables. You select the week number, and all game results for selected week are displayed. Based on this example, you can learn to create any other reports you need (p.e. yearly or monthly reports).
You can use this app for several years without any design changes (i.e. only registering new game results). In case the number of entries grows too much (so the workbook slows down), you can save the workbook as archive one (deleting from saved archive some of last years data), and after that deleting from working version score data for archived years (NB! Delete sheet rows, not table rows!).
|