How do I auto transfer rows of data from multiple sheets into a different sheet

[dpirovich]

Hello all,

I am after some help with excel. I am a PE teacher and need to find a way to cut down the processing time for our athletics carnival scoring system. One way is to use excel to automatically sort through some of the data for me. The problem is Im not savvy enough to set this up and so some help would be great.


What I want to do:
Below is an example of how I would like to set out my worksheets. There will be approx 12 worksheets (one for each form) and they will be set up as seen below.

Worksheet 1 (form M1)

Discus Event

[Row 1] Student Name, Year, Form, Throw 1, Throw 2, Throw 3, Best Throw

[Row 2] e.g jonny 8 M1 14m 15m 16m 16m


Worksheet 2 (form G1)

Discus Event

[Row 1] Student Name, Year, Form, Throw 1, Throw 2, Throw 3, Best Throw

[Row 2] e.g Jacko 8 M1 13m 15m 18m 18m


Final Scores
Discus Event

[row 1] Student Name, Year, Form, Best Throw



As seen above the last worksheet will be for the final scores. Essentially what I need is a way to send each row of student data from EACH worksheet to the "Final Scores" worksheet AND once it is at the final worksheet for it to sort itself from largest throw to smallest throw.
The purpose of this is so that after all of the data is input int other spreadsheet, I can order the best throws from highest to lowest and use the top 10 throws in my PE scoring system.

I cannot lose track of which student has thrown what and so all the data must stay together.

Some help would be great.
cheers.

DP

 

[bfreescott]

DP,

I'm not sure I really understand why you have two different "forms" for the same Discus Event, but what you need as best I can tell is a table with an auto-filter on the header row.

Once you set up the table, you can simply use the autofilter to filter on the student name and sort on throws.

But what I suspect will be of more value to you is to insert a pivot table based on your table.

If it were me doing it, I wouldn't separate your data into different forms and sheets. Just put everything into one table and then create a pivot on that table. If you're new to creating pivot tables, just google it. There is plenty of info on the net about them. Or come back to this thread and I will help you.