Moving a calclated score to a master score sheet and ranking the scores into placings

I am a real novice to excel so hope someone can help me to solve my problem.
I have a sheet that needs to be completed to calculate a score then that score needs to be transferred to a master score sheet that can be ranke into position order. How do I do this please?

I have attached a format with explanations of what I am trying to do to assist.

Score Program.xlsx

Thanks

Adrian

Hi Adrian,

I was unable to open the .xls file you had posted since I'm running Excel 2003 and that file must be for a later version. Attached below is code for populating cells with scores and sorting them by value then by name.

I attached the .xls file to this post so you can see it run if that helps.

If this doesn't help you, you can save down the .xls file you posted as an Excel 2003 file and I will take a look at it to see exactly what you are trying to do

Thanks,

Daniel

Option Explicit

Public i As Long
Public uName As String
Public uScore As Single

Sub AddScore()

Dim LastRow As Long

Sheets("Master Score").Select

frmScore.Show

' Find LastRow
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastRow = LastRow + 1

' Add new values
Cells(LastRow, 1) = uName
Cells(LastRow, 2) = uScore

' Sort Scores from highest to lowest, then names by Alphabetical Order

Range(Cells(2, 1), Cells(LastRow, 2)).Select
Selection.Sort Key1:=Range("B2"), Order1:=xlDescending, Key2:=Range("A2") _
   , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
   False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
   :=xlSortNormal
   
Range("A2").Select
   
End Sub

I want to reply an attach a new version of the spreadsheet. I have looked in FAQ and cannot see the icon or how to attach a file please could you advise?

Adrian

Thank you. I knew it would be very simple.

Adrian

Hi Cofad,

No, I want to consolidate the scores onto the first sheet. In essence you input the horse number into sheet 2, then input the scores, the total marks and percentage are calculate an then these along with the collectives are transferred to the master score sheet and ranked. This is repeate for each competitor until you have a full set of scores for all riders with them in ranked order 1st to last.

I hope that this makes sense?

Regards

Adrian

Daniel/Cofad,

Thank you very much for your input. I have added some colour coding to the orginal spreasheet (lost in conversion to 2003 I am afraid Daniel, but you had the gist anyway). Please see the explanation below, hopefully this makes it more clear?

The spreadsheet is to score a horse dressage competition. In a class there will be a number of competitors all with a unique competitor number. The judge provides a marking sheet with scores for each of the movements performed (these are represented by 1 – 30 on the “Score Calculator” sheet). The scores are input from the sheet for each competitor, identified by their competitor number. The results for that competitor are transferred to the “Class sheet”, sheet and then ranked according to the highest percentage with the total collective scores acting as the arbitrator in the event of a tie.

Thank you again.

Adrian

Score Program.xls

Score Program 2007 Ver.xlsx

Can anyone help me complete this please?

Hi Cofad, Thanks for getting back in touch. It was not a case of not to my liking, I had just not managed to explain what I wanted to do well enough to allow you to create what I am after. I appreciate your patience and tenacity, I will give it another go.
I would like 2 worksheets; the first has a list of horse riders with their name and their horses name and a score column. The second has a copy of a test sheet with a list of movements which the horse and rider must perform and the overall score and therefore placings are calculated from these movements. A paper sheet with the scores will be transferred onto the second sheet and will use the input of the horses number as the key identifier. On the press of a calculation button the score will transfer to sheet one and rank all of the competitors into 1st 2nd 3rd etc. There are also 4 individual marks that need to be transferre from sheet 2 which are used in the event of a tie. These are the 4 collective marks. There are usually 25 - 30 movements and the marks are out of either 10 or 20. The score is a percentage based on the total marks.

I hope I have explained it a bit better, its not as complicated as it sounds. If it would help I will send the example layout again?

Thank you

Adrian

Cofad, Have included calculations and explanations to help clarify.

Thank you

I suppose they could but thought that you then have an input sheet and results sheet. What ever you think is best.

If you could use one sheet then please. You do realise that the movement scores will be overwritten for each competitor and only the total and collective marks will be transferred to the score board?

Thanks

Adrian

Hi Cofad,
Thank you for the sheet. Unfortunately it is not quite what I am after. I dont need to have all of the indvidual movement scores on the score sheet. The idea was to create a form that calculated the scores then transferred the totals onto a score sheet. The form is overwritten for each competitor eg you fill in the score hit a calculate button and the totals are transferred to the score sheet and ranked. You overwrite those scores with the next competitor and repeat the process. That is why I used 2 sheets. It is the programming of the macro to take the totals and transfer them to the score sheet on a new line and then sort them that I am not clever enough to do. If you look on my exmple score sheet that is why there is only the names and a percentage. the collective scores are only there in the event of a tie on score. Hope that is helpful.

Adrian

Hi Cofad,

That is exactly what I am looking for, thank you. Scoring is based on highest percentage score with any equality being decided by the highest collective score after which it is a joint place. Hope that makes sense.
Thank you again and I really appreciate your perseverence.

Adrian