How to get a table to self update when a better value is achieved?

Hi I have a spreadsheet which I use to keep track of pupils athletics achievements throughout the season.

It comprises of several sheets
Sheet 1 is where I have several tables which are in year groups from year 7 pupils through to year 13 pupils. So a table for each year group.
In the first column of each table are the names of all the pupils in that year group. If you follow along their row it will give their times and distances of events such as 100m 800m high jump etc. their times and distances are the best from this current season. Also which is not that important to my question but is worth mentioning is that each event is also given a score out of 20. So basically to score 20 points in that event you would need to perform somewhere near to the school record. These scores are put into the cells using a formula which looks at another sheet where I have score tables with times and distances on a scale going from 1-20.

The remaining sheets are year group specific
So in the year 7 sheet what this has is a table for each pupil. In these I log all there times and distances throughout the term and the best result is what gets displayed in sheet 1.

Currently what I have to do is come next season I will have to move all the pupils names which are in year 7 for example into year 8.
They will then have a blank set of results which will gradually get filled in through that season.

My question is I would like the ability somehow of having a separate table that will keep their personal best times and distances. As they may set a PB in year 9 and not get better in year 10.

It would be good to see when they set it. Is this possible and also can this table self update itself. So if a new PB is achieved the table logs this time and disregards the last.

Speaking to someone they mentioned the possibility of pivot tables. I have never used these. I'm not sure how to do this without having lots of tables to reference maybe one for each year perhaps?

Any ideas would be great

In this case it would be REALLY helpful to see a sample of your data. It sounds to me like PivotTables are the way to go, but I think we'll need to consolidate your data first. Can you sanitize it, replacing the pupil names with something else so that we can see what you have?

Thanks here are some screen shots which i have taken

This is what sheet 1 looks likes



This table is a pupils times and distances through the season as you can see the bottom row picks out their best performance (this is from sheet "Year 7"


The table below shows the best performance (sheet 1 "Results") So if you look at "Harry" he is 4th from bottom you can see that this table just shows their best results of the year in relation to all other pupils (for some reason his 100 metre time is missing). Also next to each event is a score which is retrieved from a score table (green below)


This table is from the sheet "Score Tables"


As you can see I don't really have completely raw data so not sure how a pivot table would work. Possibly using a pupils individual table where they have lots of times? However would I need to keep separate pupil table for each year. For example Harry in year 7 I would need to keep a table for each year as he progresses through the school so the pivot table can simply pull out his best ever time or distance?

Is there any way you could email me your workbook? I'm pretty sure I can consolidate your data source to make this happen. Can't do it through screenshots though, and I don't have time to type all that out. Ken@ (I'm sure you can figure out the domain part.)