Hi all,
I'm involved with a local indoor bell target shooting group where we shoot at targets & try to ring the bell which is achieved by hitting the very center of the target - This is worth 6 points, with other scores received the further away from the center. I'm not trying to do anything particularly fancy with regards to noting the scores, but am recording the score for each player along with the number of bells they've scored in each round. We use the number of bells to help with tie break scenarios, but do allow joint 1st, 2nd & 3rd places if we can't split the difference using the number of bells. i.e. 3 players can get 1st place if they have the same score & the same number of bells. Pleasingly enough I've managed to account for this in my score-sheet & it seems to work well.
However I've been trying to improve on the score sheet, but seem to be getting stuck. I wanted to create a dropdown list of the players names & have it so they could only be selected once. I found an answer to this using google & it works perfectly for one sheet, but if I want to keep track of each weeks scores in a separate sheet the drop down names list no longer works & I understand it's due to needing a unique name list / helper column for each weeks score sheet. Surely there must be another way?
Secondly I was hoping to then be able to create a Running total sheet that would loop through all the weekly score sheets (named something like Shoot_08092020 or Shoot_15092020) & calculate the number of league points per player (this is why I wanted to ensure the names being used are unique). The players are allocated points if they've come 1st, 2nd or 3rd in any of the weekly shoots. 1st is valued at 3 point, 2nd at 2 & 3rd at 1, so if you came first in one shoot & 2nd in another you'd have a total of 5 point in the league. I'm thinking this'll probably require vba code, but I've literally just got as far as looping through all the sheet names & producing a msgbox with the sheet name.
Is anyone able to tell me if I'm at least looking at the right approach to this or hopefully provide some pointers / tips / much needed help?
I'm happy to provide the existing sheet if it helps. Thanks in advance
I'm involved with a local indoor bell target shooting group where we shoot at targets & try to ring the bell which is achieved by hitting the very center of the target - This is worth 6 points, with other scores received the further away from the center. I'm not trying to do anything particularly fancy with regards to noting the scores, but am recording the score for each player along with the number of bells they've scored in each round. We use the number of bells to help with tie break scenarios, but do allow joint 1st, 2nd & 3rd places if we can't split the difference using the number of bells. i.e. 3 players can get 1st place if they have the same score & the same number of bells. Pleasingly enough I've managed to account for this in my score-sheet & it seems to work well.
However I've been trying to improve on the score sheet, but seem to be getting stuck. I wanted to create a dropdown list of the players names & have it so they could only be selected once. I found an answer to this using google & it works perfectly for one sheet, but if I want to keep track of each weeks scores in a separate sheet the drop down names list no longer works & I understand it's due to needing a unique name list / helper column for each weeks score sheet. Surely there must be another way?
Secondly I was hoping to then be able to create a Running total sheet that would loop through all the weekly score sheets (named something like Shoot_08092020 or Shoot_15092020) & calculate the number of league points per player (this is why I wanted to ensure the names being used are unique). The players are allocated points if they've come 1st, 2nd or 3rd in any of the weekly shoots. 1st is valued at 3 point, 2nd at 2 & 3rd at 1, so if you came first in one shoot & 2nd in another you'd have a total of 5 point in the league. I'm thinking this'll probably require vba code, but I've literally just got as far as looping through all the sheet names & producing a msgbox with the sheet name.
Is anyone able to tell me if I'm at least looking at the right approach to this or hopefully provide some pointers / tips / much needed help?
I'm happy to provide the existing sheet if it helps. Thanks in advance