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:
The column G, referenced by the previous formula, is a helper column with the formula:
The rankings columns are populated by the formula:
The individual stroke sheets are populated by the formula:
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.
Bookmarks