+ Reply to Thread
Results 1 to 31 of 31

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

  1. #1
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

    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

  2. #2
    Forum Contributor
    Join Date
    10-19-2012
    Location
    Omaha, Nebraska USA
    MS-Off Ver
    Excel 2010
    Posts
    249

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

    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



    Please Login or Register  to view this content.
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Adrian,

    I looked at your spreadsheet, and I'm having some trouble understanding exactly what needs to be found and what needs to be placed where. Perhaps you could elaborate more, and I'm sure we can get you something figured out.
    ------------------------
    W2

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,235

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

    I agree with Cofad. perhaps you could include in your file, some samples of what your expected outcome is, and how you arrived at that?

    @djb, i am running 2007 now, but when i was using 2003, i could also open .xlsx files...xlsx is the file extention (format) for excel files from 2007 and higher
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

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

    to attach a sample workbook:

    When you are in Advanced reply, click on the paperclip to open the upload window.

    View Pic
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  7. #7
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  8. #8
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    Thank you. I knew it would be very simple.

    Adrian

  9. #9
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Are you wanting to add a sheet for each horse/competitor and then summarize the score data for each sheet into a master sheet?

  10. #10
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  11. #11
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    So you want to overwrite the individual score data for each horse and not have a record of that information?

    Edit: The reason I'm asking is that a userform could be created to input the data and then save the data to the score sheet. That way the score calculator is a little more straightforward to work with.
    Last edited by Cofad; 01-02-2013 at 06:10 PM.

  12. #12
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    So, I've got a new spreadsheet that I think might do what you need. I've added a new sheet called "Score Sheet" which has a button that opens a user form. This form can be used to enter the data and then saves it to the spreadsheet. I think this is something along the lines of what you wanted to achieve. Let me know what you think.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    Hi Cofad,

    The score information would be stored on the Master sheet but yes it would be overwritten on the calc sheet. Understanding how to move the data from the calc sheet to the master sheet each time was the crux of my problem. I am trying to avoid having a calculation sheet for every horse as you dont need to keep the components of the score just add up the score and calculate the percentage accurately. It sound as if a userform might be the answer; but that is well beyond me.

    Regards

    Adrian

  14. #14
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    I think I understand now. Did you take a look at the last workbook I posted? It has a userform in it. If that doesn't work for you, I could try to do it the way you had originally pursued.

  15. #15
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    Hi Cofad,

    I think I confused the issue with my last but one post, my apologies. Your workbook did not appear when I opened your post so no I had not. I have now had a look and like the userform a lot. I just have the following comments:

    There are not always exactly the same number of movements in a test as there are different tests, I chose 30 as an example.
    The collective scores are include in the overall total as well as being a total in their own right.
    A list of competitors would be prepared prior to the competition so I was thinking that some kind of lookup would pick the rider and hore names from the list when the number was input, saving typing time.
    Can the scores be typed on a user form or do you have to select from a drop down, again just a time thing and less chance of error.

    Thank you very much for your help, its so hard to explain what I am trying to achieve, I appreciate your patience.

    Adrian

  16. #16
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Adrian,

    What would be the maximum number of movements? For example if there were at most 60 movements, I could put 60 spots on the form and anytime there were less, say 30, the others could be left at 0 without affecting the total.

    You can easily make the riders and horses a drop down menu. I'd just add a sheet and input the information there and then link it to the fields on the form.

    You can type the scores into the combo box. When I was testing it, I found it easiest to tab through all scores and enter the values with my keypad. I put the combo boxes there just to give an idea of the score numbers.

    I'll try and work on it for you today.

  17. #17
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Check out the new spreadsheet. I updated the userform, and I also put a transfer score button on the score calculator page to do what you had originally intended.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    Can anyone help me complete this please?

  19. #19
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    I'm not gonna re-read through everything, especially since my last solution was not to your liking. Please explain exactly what you are looking for with specific items and attach a layout, and I will give it another try.

  20. #20
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  21. #21
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Yes, upload the exact format you want the spreadsheet in.

  22. #22
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    Cofad, Have included calculations and explanations to help clarify.

    Thank you
    Attached Files Attached Files

  23. #23
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    It seems to me that two sheets is unnecessary. Could you not input the calculations and compute the scores on the same sheet?

  24. #24
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

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

  25. #25
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Here are why I would choose a single sheet system:
    • It is simpler to implement and change in the future.
    • You can leave all the movement scores on the sheet which will make it easier to change a score and verify correctness.
    • It will be more user friendly for others to use.

    I can't really think of a benefit of a two sheet system.

    As this is your project, the choice is ultimately yours. I'm trying to help you achieve your goals, so if you really want a two sheet system, that is doable. Let me know which option you want to pursue.

  26. #26
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  27. #27
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    With the two sheet option yes, but if you go with a single sheet option you could keep all of the scores for all of the competitors for future reference. I'll create an example.
    Attached Files Attached Files
    Last edited by Cofad; 03-24-2013 at 10:11 PM.

  28. #28
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  29. #29
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Adrian,

    Try this. I suggest adding the horse and rider name to the score calculator and having it move those values over as well.

    Let me know if this is close to what you want, and I will then work on trying to calculate a position value and sorting the scores.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    12-28-2012
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    14

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

    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

  31. #31
    Forum Contributor
    Join Date
    09-22-2012
    Location
    Berkeley, CA
    MS-Off Ver
    Excel 2010
    Posts
    112

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

    Adrian,

    Try this. It's probably a little buggy, but it's gets the just of it. If you have issues with it, let me know, and I'll see what I can do.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1