Score sheets - select names once per sheet & produce running total from all score sheets

craigey1

New Member
Joined
Apr 6, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Ezguy4u

Board Regular
Joined
Feb 10, 2010
Messages
118
Office Version
  1. 2019
Platform
  1. Windows
I am going to vote for providing the existing sheet or workbook. Make sure you use that XLB to post your sheets if you can.
 

craigey1

New Member
Joined
Apr 6, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
Ok, wasn't sure of the best way of providing the whole workbook so am providing the sheets individually using XL2BB. Hope this makes thing a bit clearer.

This is the sheet containg the list of shooterNames (used for the drop down list)

Bagworth_shooting_Scoresheet.xlsm
ABC
1Jim Clive
2Clive2Mo
3Mary Fred
4Mo4Mel
5John Jef
6Fred6Ash
7Mic  
8James  
9Barry  
10Mark  
11Steve  
12Gary  
13Harry  
14Bob  
15Mel15 
16Jef16 
17jerry  
18Ash18 
ShooterNames
Cell Formulas
RangeFormula
B1:B18B1=IF(A1<>"",IF(COUNTIF(Shoot_1!$B:$B,A1)>=1,"",ROW()),"")
C1:C18C1=IF(ROW(A1)-ROW(A$1)+1>COUNT($B:$B),"",INDEX(A:A,SMALL($B:$B,1+ROW(A1)-ROW(A$1))))
Cells with Data Validation
CellAllowCriteria
A1:A18Custom=COUNTIF(A:A,A1)<2




This is the sheet named Shoot_1 & is the results from the 1st week

Bagworth_shooting_Scoresheet.xlsm
ABCDEFGHIJKLMNOP
1Round 1Round 2Round 3Round 4TotalPosition / RankTop Scorers
2NameBellsScoreBellsScoreBellsScoreBellsScoreBellsScore
31Mark35243346121811Second
42James332222249112 
53Mic23453635121912First
64Jim252324127145 
7 
81Steve342222229101 
92Harry1513434410157 
103Gary132245259156 
114Mary232524258179ThirdTotal Bells
12 112
131John132223348124 
142Barry342223229112 
153jerry132335268179Third
164Bob2324245411158 
17  
181  
192  
203  
214  
22  
231  
242  
253  
264  
27  
281  
292  
303  
314  
Shoot_1
Cell Formulas
RangeFormula
K13:L16,K8:L11,K3:L6K3=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
N13:N31,N8:N11,N3:N6N3=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
O3:O31O3=IF(ISBLANK($L3),"",LOOKUP(SUM(IF($N3<$N$3:$N$31,1/COUNTIF($N$3:$N$31,$N$3:$N$31)))+1,{1,2,3,4},{"First","Second","Third",""}))
P12P12=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
B8:B11,B13:B16,B3:B6,B18:B21,B23:B26,B28:B31List=Namecheck
D8:D11,F8:F11,H8:H11,J8:J11,D13:D16,F13:F16,H13:H16,J13:J16,D3:D6,F3:F6,H3:H6,J3:J6,D18:D21,F18:F21,H18:H21,J18:J21,D23:D26,F23:F26,H23:H26,J23:J26,D28:D31,F28:F31,H28:H31,J28:J31Custom=AND(D8>=0, D8<=30,D8>=C8)
E13:E16,E23:E26,C8:C11,G3:G6,I3:I6,C3:C6,E3:E6,E18:E21,G18:G21,I18:I21,C18:C21,G23:G26,I23:I26,C23:C26,E8:E11,G8:G11,I8:I11,G13:G16,I13:I16,C13:C16,E28:E31,G28:G31,I28:I31,C28:C31Whole numberbetween 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11Whole numberbetween 0 and 24
L23:L26,L3:L6,L8:L11,L13:L16,L18:L21,L28:L31Whole numberbetween 0 and 120




This is the sheet named Shoot_2 & is the results from the 2nd week - You can see the issue with the unique drop down list selection

Bagworth_shooting_Scoresheet.xlsm
ABCDEFGHIJKLMNOP
1Round 1Round 2Round 3Round 4TotalPosition / RankTop Scorers
2NameBellsScoreBellsScoreBellsScoreBellsScoreBellsScore
31Mo35241346101811First
42Mo332222249113 
53Mo23254525101811First
64Mo152324126145 
7 
81Mo342222229101 
92Mel151323448156Third
103Clive132245158156Third
114Mel232522258156ThirdTotal Bells
12 99
131Mel132223348124 
142Jef341223228112 
153Mo1423252671810Second
164Clive232424248156Third
17  
181  
192  
203  
214  
22  
231  
242  
253  
264  
27  
281  
292  
303  
314  
Shoot_2
Cell Formulas
RangeFormula
K13:L16,K8:L11,K3:L6K3=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
N13:N31,N8:N11,N3:N6N3=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
O3:O31O3=IF(ISBLANK($L3),"",LOOKUP(SUM(IF($N3<$N$3:$N$31,1/COUNTIF($N$3:$N$31,$N$3:$N$31)))+1,{1,2,3,4},{"First","Second","Third",""}))
P12P12=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
D8:D11,F8:F11,H8:H11,J8:J11,D13:D16,F13:F16,H13:H16,J13:J16,D3:D6,F3:F6,H3:H6,J3:J6,D18:D21,F18:F21,H18:H21,J18:J21,D23:D26,F23:F26,H23:H26,J23:J26,D28:D31,F28:F31,H28:H31,J28:J31Custom=AND(D8>=0, D8<=30,D8>=C8)
E13:E16,E23:E26,C8:C11,G3:G6,I3:I6,C3:C6,E3:E6,E18:E21,G18:G21,I18:I21,C18:C21,G23:G26,I23:I26,C23:C26,E8:E11,G8:G11,I8:I11,G13:G16,I13:I16,C13:C16,E28:E31,G28:G31,I28:I31,C28:C31Whole numberbetween 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11Whole numberbetween 0 and 24
L23:L26,L3:L6,L8:L11,L13:L16,L18:L21,L28:L31Whole numberbetween 0 and 120
B3:B31List=Namecheck



This is the running total / summary sheet (I've got my nested if statement to work to look at the first of the Shoot results, but not sure how to get this to sum all the results from all the shoot result sheets)
Bagworth_shooting_Scoresheet.xlsm
ABCD
1Total points3 for a win, 2 for 2nd & 1 for 3rd
2Namepoints
3Jim 
4Clive 
5Mary1
6Mo 
7John 
8Fred 
9Mic3
10James 
11Barry 
12Mark2
13Steve 
14Gary 
15Harry 
16Bob 
17Mel 
18Jef 
19jerry1
20Ash 
Sheet2
Cell Formulas
RangeFormula
A2A2=IF(Shoot_2!B2=0,"",Shoot_2!B2)
A3:A20A3=IF(ShooterNames!A1<>0,ShooterNames!A1,"")
B3:B20B3=IFNA(IF(INDEX(Shoot_1!$B$3:$O$31,MATCH(A3,Shoot_1!$B$3:$B$31,0),14)="First","3",IF(INDEX(Shoot_1!$B$3:$O$31,MATCH(A3,Shoot_1!$B$3:$B$31,0),14)="Second","2",IF(INDEX(Shoot_1!$B$3:$O$31,MATCH(A3,Shoot_1!$B$3:$B$31,0),14)="Third","1",""))),"")
 

craigey1

New Member
Joined
Apr 6, 2020
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
I've updated the weekly score sheets to include the points awarded. i.e. 3 points for 1st etc as I believe this makes things easier / clearer for the Running Total sheet. However it looks like I have to edit the formula to include each new weekly score sheet. Is there a better way?

Score sheet (week 1)
Bagworth_shooting_Scoresheet.xlsm
ABCDEFGHIJKLMNOP
1Round 1Round 2Round 3Round 4TotalPosition / RankRound PositionRound Points
2NameBellsScoreBellsScoreBellsScoreBellsScoreBellsScore
31Mark35243346121811Second2
42James332222249112 0
53Mic23453635121912First3
64Jim252324127145 0
7  
81Steve342222229101 0
92Harry1513434410157 0
103Gary132245259156 0
114Mary232524258179Third1Total Bells
12  112
131John132223348124 0
142Barry342223229112 0
153jerry132335268179Third1
164Bob2324245411158 0
Shoot_1
Cell Formulas
RangeFormula
K13:L16,K8:L11,K3:L6K3=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
M13:M16,M8:M11,M3:M6M3=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
N3:N16N3=IF(ISBLANK($L3),"",LOOKUP(SUM(IF($M3<$M$3:$M$31,1/COUNTIF($M$3:$M$31,$M$3:$M$31)))+1,{1,2,3,4},{"First","Second","Third",""}))
O3:O16O3=IF(ISBLANK($L3),"",LOOKUP(SUM(IF($M3<$M$3:$M$31,1/COUNTIF($M$3:$M$31,$M$3:$M$31)))+1,{1,2,3,4},{3,2,1,0}))
P12P12=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.
Cells with Data Validation
CellAllowCriteria
B8:B11,B13:B16,B3:B6,B18:B21,B23:B26,B28:B31List=Namecheck
D8:D11,F8:F11,H8:H11,J8:J11,D13:D16,F13:F16,H13:H16,J13:J16,D3:D6,F3:F6,H3:H6,J3:J6,D18:D21,F18:F21,H18:H21,J18:J21,D23:D26,F23:F26,H23:H26,J23:J26,D28:D31,F28:F31,H28:H31,J28:J31Custom=AND(D8>=0, D8<=30,D8>=C8)
E13:E16,E23:E26,C8:C11,G3:G6,I3:I6,C3:C6,E3:E6,E18:E21,G18:G21,I18:I21,C18:C21,G23:G26,I23:I26,C23:C26,E8:E11,G8:G11,I8:I11,G13:G16,I13:I16,C13:C16,E28:E31,G28:G31,I28:I31,C28:C31Whole numberbetween 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11Whole numberbetween 0 and 24


Summary sheet
Bagworth_shooting_Scoresheet.xlsm
AB
1Total points
2Namepoints
3Jim0
4Clive4
5Mary3
6Mo2
7John0
8Fred4
9Mic1
10James0
11Barry0
12Mark2
13Steve0
14Gary0
15Harry0
16Bob0
17Mel1
18Jef3
19jerry3
20Ash0
Sheet2
Cell Formulas
RangeFormula
A2A2=IF(Shoot_2!B2=0,"",Shoot_2!B2)
A3:A20A3=IF(ShooterNames!A1<>0,ShooterNames!A1,"")
B3:B20B3=SUM(IFNA(INDEX(Shoot_1!$B$3:$O$31,MATCH(A3,Shoot_1!$B$3:$B$31,0),14),0),IFNA(INDEX(Shoot_2!$B$3:$O$31,MATCH(A3,Shoot_2!$B$3:$B$31,0),14),0),IFNA(INDEX(Shoot_3!$B$3:$O$31,MATCH(A3,Shoot_3!$B$3:$B$31,0),14),0))
 

Watch MrExcel Video

Forum statistics

Threads
1,112,995
Messages
5,543,185
Members
410,584
Latest member
Bluefox68
Top