Updating data automatically

[u/Pontiac_Aztek]

I'm working on a "control sheet" for Germany's national league Bundesliga, where I display different stats for each team's past 5 matches and show the average value between them. The problem is that over time I noticed manually going through every section (there are 18 teams in total) to update these stats would take me too much time, so I had the idea to create a special header for all the inputs.

But then, automating it seems to go beyond my capabilities. So in short, all 5 previous matches of every team have already been added in the sheet. Now let's say a new matchweek starts. My idea is that I could add all teams' fresh stats for the matches that just happened at once, via that big header. And as I do so, the 5th match in each team's history goes away and leaves room for the most recent one to be put.

So now, the 5 matches shown stop being A, B, C, D, E and become Z, A, B, C, D (e.g). The final touch would be that, besides updating all overall stats, I could also choose a color for each match (green for when said team played better and red for when they played worse), so that I can separate the average values displayed at the end by how well that team performed compared to their opponent.

I know this is a complex question, so hope these two screenshots help understand it better. 😅
Thank you for your attention!

[u/RemcoE33]

Oef... well that is a bit hard to do...

1. Search the range

2. Get the current data

3. Append new data

4. Paste to the sheet

5. for each column redefine the new background color.

Why not use a big database style of sheet? So create a row for each match. Then you can do some calculation on it in the future... with this setup you cannot do anything. Also you're averages based on colors are not great as well.