Automatic Leader board for Swimming

[Kiiyul]

Hey guys,

I am wanting to create a ranking leader board for all of the swim squads I currently coach based on time for each stroke. I am completely new to excel so any help would be really appreciated.

I currently have the setup as the specific squad on different sheets, and will be wanting the times for all swimmers for that specific stroke, so the formula will need to pull information from 4 different sheets.

Therefore lets say Swimmer 1 in Squad E, swims Fly in 30.1, Swimmer A in Squad D swims fly in 29.7, I would want this displayed on the same ranking sheet. The a different ranking sheet for Backstroke, breaststroke and freestyle. It would also need to update if the swimmer achieves a new personal best.

I have attached a quick screenshot of an example of the spreadsheet I am using.

Excel Swim Spreadsheet.png

Thank you for your help

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pete

[Kiiyul]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon does not work.

Hope this helps.

Pet

Done I think

[Kiiyul]

bump to the top

[JohnTopley]

This would much easier if you added a "Swim" column i.e "Swim A", "Swim B" etc to your data in the "Swim" tab so that it was in one sheet.

[Kiiyul]

This would much easier if you added a "Swim" column i.e "Swim A", "Swim B" etc to your data in the "Swim" tab so that it was in one sheet.

The sheets along the bottom are the different squads each swimmer is in. Is it not possible with the variety of squads on each sheet. I would prefer to keep it this way, unless it isn't possible then I should be able to put all the squads on one sheet and the ranking for each stroke on a separate sheet.

[JeteMc]

I worked on this yesterday, then held off posting to see if a 'multiple sheet' solution would be given. This does basically what John suggests in that it combines the data into a single sheet using formulas that reference the in individual team sheets. Since the rankings sheet was set up to accommodate 70 swimmers, I assumed that there could be 12 on each of the five teams. The 'Master' sheet will update as new names are added, or existing names are deleted. The 'Master' sheet also ranks each stroke so that swimmers with times that tie are listed along with their times.
The Formula that retrieves the names and times is:

Please Login or Register  to view this content.

The column G, referenced by the previous formula, is a helper column with the formula:

Please Login or Register  to view this content.

The rankings columns are populated by the formula:

Please Login or Register  to view this content.

The individual stroke sheets are populated by the formula:

Please Login or Register  to view this content.

Note: Times that were displayed as min.sec.tenths have been modified to sec.tenths i.e. 1.04.3 was modified to 64.3
Note: The master sheet could be hidden so that to the end user it appears that rankings come directly from the individual team sheets.
Let us know if you have any questions.

[Kiiyul]

I worked on this yesterday, then held off posting to see if a 'multiple sheet' solution would be given. This does basically what John suggests in that it combines the data into a single sheet using formulas that reference the in individual team sheets. Since the rankings sheet was set up to accommodate 70 swimmers, I assumed that there could be 12 on each of the five teams. The 'Master' sheet will update as new names are added, or existing names are deleted. The 'Master' sheet also ranks each stroke so that swimmers with times that tie are listed along with their times.
The Formula that retrieves the names and times is:

Please Login or Register  to view this content.

The column G, referenced by the previous formula, is a helper column with the formula:

Please Login or Register  to view this content.

The rankings columns are populated by the formula:

Please Login or Register  to view this content.

The individual stroke sheets are populated by the formula:

Please Login or Register  to view this content.

Note: Times that were displayed as min.sec.tenths have been modified to sec.tenths i.e. 1.04.3 was modified to 64.3
Note: The master sheet could be hidden so that to the end user it appears that rankings come directly from the individual team sheets.
Let us know if you have any questions.

Hey,

Apologies for the delay in replying, but thank you so much for doing this. It works very well and I could have never have worked it out so really appreciate your help.

I have been trying to understand what each formula does as my original document has contact details etc which means the times have gone into different cells. Along with trying to work out how to change the amount per squad as it is more than 12 per squad. I think I have managed it by changing the formula in the "helper" column to

Please Login or Register  to view this content.

Not sure if this is correct, however it seems to be working.

Again, thanks for the help.

[JeteMc]

Been away from the computer for a while.
Formula looks correct, best indication is that it is giving expected results, however running Evaluate Formula from the Formulas tab is another way to confirm that it is doing what you want.
As for the help, You're Welcome and thank you for the feedback. If the solution works, as it seems to, please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.