Athletics program with excel

Hi guys

Kinda stuck with a small problem. I have been tasked with creating a program for athletics meeting at a primary school with excel. I am a teacher...

There are heats, final and a summary sheet.
Column headings are: Name, Surname, Time, Rank. for each event and age group.

In the heats sheet names are entered beforehand. Then on the day, the times of the athletes are entered. Now I can do a =rank() to determine the top athletes.

What I then want is to carry the top 12 names over to the final sheet. Then, once the final has been run, those times are entered. Again, can do the rank.

The summary sheet the lists the final results, ie the athletes from fastest to slowest.

So my question is - how do I get the names carried over from the specific sheets according to their rank?

I tried =Index but the doesn't display the correct names.

Just pointing me in the right direction would help. Not asking to do the whole thing for me. I want to struggle and figure it out (only way to learn) but just need a nudge in the right direction.

Thanks

Ok will do so. Thanks for quick reply!!

I have an unknown number of heats. The top 12 from all the heats need to go to the final. They run there. Once they have run, their times are entered. These times are ranked and then placed on the summary page. Fastest time first.

Wow you guys are awesome!!! Thanks so much!

Twiggywales - might be a stupid question, but that is kind of why I am here ( to learn...) - could you breakdown your iferror formula so that I understand how it is used...

Did read up that it is used when you want to trap and handle an error.

Again thanks so much!