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

[craigey1]

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

 

[Ezguy4u]

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]

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
	A	B	C
1	Jim		Clive
2	Clive	2	Mo
3	Mary		Fred
4	Mo	4	Mel
5	John		Jef
6	Fred	6	Ash
7	Mic
8	James
9	Barry
10	Mark
11	Steve
12	Gary
13	Harry
14	Bob
15	Mel	15
16	Jef	16
17	jerry
18	Ash	18
ShooterNames

Cell Formulas
Range	Formula
B1:B18	B1	=IF(A1<>"",IF(COUNTIF(Shoot_1!$B:$B,A1)>=1,"",ROW()),"")
C1:C18	C1	=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
Cell	Allow	Criteria
A1:A18	Custom	=COUNTIF(A:A,A1)<2

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

Bagworth_shooting_Scoresheet.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Round 1		Round 2		Round 3		Round 4		Total			Position / Rank	Top Scorers
2		Name	Bells	Score	Bells	Score	Bells	Score	Bells	Score	Bells	Score
3	1	Mark	3	5	2	4	3	3	4	6	12	18		11	Second
4	2	James	3	3	2	2	2	2	2	4	9	11		2
5	3	Mic	2	3	4	5	3	6	3	5	12	19		12	First
6	4	Jim	2	5	2	3	2	4	1	2	7	14		5
7
8	1	Steve	3	4	2	2	2	2	2	2	9	10		1
9	2	Harry	1	5	1	3	4	3	4	4	10	15		7
10	3	Gary	1	3	2	2	4	5	2	5	9	15		6
11	4	Mary	2	3	2	5	2	4	2	5	8	17		9	Third	Total Bells
12																112
13	1	John	1	3	2	2	2	3	3	4	8	12		4
14	2	Barry	3	4	2	2	2	3	2	2	9	11		2
15	3	jerry	1	3	2	3	3	5	2	6	8	17		9	Third
16	4	Bob	2	3	2	4	2	4	5	4	11	15		8
17
18	1
19	2
20	3
21	4
22
23	1
24	2
25	3
26	4
27
28	1
29	2
30	3
31	4
Shoot_1

Cell Formulas
Range	Formula
K13:L16,K8:L11,K3:L6	K3	=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
N13:N31,N8:N11,N3:N6	N3	=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
O3:O31	O3	=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",""}))
P12	P12	=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B8:B11,B13:B16,B3:B6,B18:B21,B23:B26,B28:B31	List	=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:J31	Custom	=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:C31	Whole number	between 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11	Whole number	between 0 and 24
L23:L26,L3:L6,L8:L11,L13:L16,L18:L21,L28:L31	Whole number	between 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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Round 1		Round 2		Round 3		Round 4		Total			Position / Rank	Top Scorers
2		Name	Bells	Score	Bells	Score	Bells	Score	Bells	Score	Bells	Score
3	1	Mo	3	5	2	4	1	3	4	6	10	18		11	First
4	2	Mo	3	3	2	2	2	2	2	4	9	11		3
5	3	Mo	2	3	2	5	4	5	2	5	10	18		11	First
6	4	Mo	1	5	2	3	2	4	1	2	6	14		5
7
8	1	Mo	3	4	2	2	2	2	2	2	9	10		1
9	2	Mel	1	5	1	3	2	3	4	4	8	15		6	Third
10	3	Clive	1	3	2	2	4	5	1	5	8	15		6	Third
11	4	Mel	2	3	2	5	2	2	2	5	8	15		6	Third	Total Bells
12																99
13	1	Mel	1	3	2	2	2	3	3	4	8	12		4
14	2	Jef	3	4	1	2	2	3	2	2	8	11		2
15	3	Mo	1	4	2	3	2	5	2	6	7	18		10	Second
16	4	Clive	2	3	2	4	2	4	2	4	8	15		6	Third
17
18	1
19	2
20	3
21	4
22
23	1
24	2
25	3
26	4
27
28	1
29	2
30	3
31	4
Shoot_2

Cell Formulas
Range	Formula
K13:L16,K8:L11,K3:L6	K3	=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
N13:N31,N8:N11,N3:N6	N3	=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
O3:O31	O3	=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",""}))
P12	P12	=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
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:J31	Custom	=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:C31	Whole number	between 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11	Whole number	between 0 and 24
L23:L26,L3:L6,L8:L11,L13:L16,L18:L21,L28:L31	Whole number	between 0 and 120
B3:B31	List	=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
	A	B	C	D
1	Total points			3 for a win, 2 for 2nd & 1 for 3rd
2	Name	points
3	Jim
4	Clive
5	Mary	1
6	Mo
7	John
8	Fred
9	Mic	3
10	James
11	Barry
12	Mark	2
13	Steve
14	Gary
15	Harry
16	Bob
17	Mel
18	Jef
19	jerry	1
20	Ash
Sheet2

Cell Formulas
Range	Formula
A2	A2	=IF(Shoot_2!B2=0,"",Shoot_2!B2)
A3:A20	A3	=IF(ShooterNames!A1<>0,ShooterNames!A1,"")
B3:B20	B3	=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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Round 1		Round 2		Round 3		Round 4		Total		Position / Rank	Round Position	Round Points
2		Name	Bells	Score	Bells	Score	Bells	Score	Bells	Score	Bells	Score
3	1	Mark	3	5	2	4	3	3	4	6	12	18	11	Second	2
4	2	James	3	3	2	2	2	2	2	4	9	11	2		0
5	3	Mic	2	3	4	5	3	6	3	5	12	19	12	First	3
6	4	Jim	2	5	2	3	2	4	1	2	7	14	5		0
7
8	1	Steve	3	4	2	2	2	2	2	2	9	10	1		0
9	2	Harry	1	5	1	3	4	3	4	4	10	15	7		0
10	3	Gary	1	3	2	2	4	5	2	5	9	15	6		0
11	4	Mary	2	3	2	5	2	4	2	5	8	17	9	Third	1	Total Bells
12																112
13	1	John	1	3	2	2	2	3	3	4	8	12	4		0
14	2	Barry	3	4	2	2	2	3	2	2	9	11	2		0
15	3	jerry	1	3	2	3	3	5	2	6	8	17	9	Third	1
16	4	Bob	2	3	2	4	2	4	5	4	11	15	8		0
Shoot_1

Cell Formulas
Range	Formula
K13:L16,K8:L11,K3:L6	K3	=IF(OR(ISBLANK(C3),ISBLANK(E3),ISBLANK(G3),ISBLANK(I3)),"",SUM(C3,E3,G3,I3))
M13:M16,M8:M11,M3:M6	M3	=IF(ISBLANK($L3),"",RANK.EQ($L3, $L$3:$L$31,1)+COUNTIFS($L$3:$L$31, $L3, $K$3:$K$31, "<" & $K3))
N3:N16	N3	=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:O16	O3	=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}))
P12	P12	=SUM($K$3:$K$31)
Press CTRL+SHIFT+ENTER to enter array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B8:B11,B13:B16,B3:B6,B18:B21,B23:B26,B28:B31	List	=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:J31	Custom	=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:C31	Whole number	between 0 and 6
K3:K6,K28:K31,K13:K16,K23:K26,K18:K21,K8:K11	Whole number	between 0 and 24

Summary sheet

Bagworth_shooting_Scoresheet.xlsm
	A	B
1	Total points
2	Name	points
3	Jim	0
4	Clive	4
5	Mary	3
6	Mo	2
7	John	0
8	Fred	4
9	Mic	1
10	James	0
11	Barry	0
12	Mark	2
13	Steve	0
14	Gary	0
15	Harry	0
16	Bob	0
17	Mel	1
18	Jef	3
19	jerry	3
20	Ash	0
Sheet2

Cell Formulas
Range	Formula
A2	A2	=IF(Shoot_2!B2=0,"",Shoot_2!B2)
A3:A20	A3	=IF(ShooterNames!A1<>0,ShooterNames!A1,"")
B3:B20	B3	=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))