Results 1 to 4 of 4

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Thanks here are some screen shots which i have taken

    This is what sheet 1 looks likes
    Click image for larger version. 

Name:	Screen Shot 2013-07-10 at 09.17.26.jpg 
Views:	25 
Size:	102.8 KB 
ID:	1477


    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"
    Name:  pupil tables.png
Views: 24
Size:  25.1 KB


    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)
    Click image for larger version. 

Name:	year 7 table.png 
Views:	25 
Size:	51.7 KB 
ID:	1476

    This table is from the sheet "Score Tables"
    Click image for larger version. 

Name:	score tables.png 
Views:	26 
Size:	69.2 KB 
ID:	1475

    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?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,274
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    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.)

    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •