Hadicapped gold scorecard help

[mikewohlwend]

I am making a scorecard for a golf trip that and want to make it fill itself out. I have some guys that have handicaps over 18 and they need to get more than 1 stroke per hole, but they can't get more than 1 on a par 3. The strokes are based on the holes handicaps from smallest to largest.

I have been able to figure out how to get it to auto populate for someone that's 0-18, I just can't figure out the multiple strokes per hole with over 18.

I don't know how to upload what I have so that I can get some suggestions.

 

[awoohaw]

Welcome to the forum. But, please assume the forum knows nothing about anything. In your case, GOLF and GOLF Handicapping.

Can you give us a table showing the hole number, the par for the hole, and the handicap rating for each hole? Try to post a mini workbook using the xl2bb add in, if you can't use that then copy and past a range of cells as a table. Images, while helpful mean the forum needs to recreate your scenario completely.

Thanks in advance!

 

[AhoyNC]

See attached example.

Golf habdicap per hole.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	17	5	11	9	15	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	1	0	0	1	0	0	0	1	0		0	0	0	0	1	0	0	0	1
7		Score	4	5	3	4	4	4	2	5	5	36	5	4	3	4	6	4	3	3	4	36	72
8		Net	3	5	3	3	4	4	2	4	5	33	5	4	3	4	5	4	3	3	3	34	67
9
10	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
11		Score	5	4	4	6	6	5	3	7	5	45	5	4	4	6	7	4	4	4	5	43	88
12		Net	4	3	3	5	5	4	2	6	4	36	4	3	3	5	6	3	3	3	4	34	70
13
14	Golfer4	24	2	1	1	2	1	1	1	2	1		1	1	1	1	2	1	2	1	2
15		Score	5	4	5	7	5	6	4	8	5	49	6	5	4	4	7	6	5	3	6	46	95
16		Net	3	3	4	5	4	5	3	6	4	37	5	4	3	3	5	5	3	2	4	34	71
Sheet3

Cell Formulas
Range	Formula
L4,V15:V16,L15:L16,V11:V12,L11:L12,V7:V8,L7:L8,V4	L4	=SUM(C4:K4)
W4,W15:W16,W11:W12,W7:W8	W4	=L4+V4
C6:K6,M14:U14,C14:K14,M10:U10,C10:K10,M6:U6	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
C8:K8,M8:U8,C16:K16,M16:U16,C12:K12,M12:U12	C8	=C7-C6

 

[Michael M]

and keep in mind, if you are doing a trip, you will probably play different courses. In which case the indexes will change each time.

 

[mikewohlwend]

Welcome to the forum. But, please assume the forum knows nothing about anything. In your case, GOLF and GOLF Handicapping.

Can you give us a table showing the hole number, the par for the hole, and the handicap rating for each hole? Try to post a mini workbook using the xl2bb add in, if you can't use that then copy and past a range of cells as a table. Images, while helpful mean the forum needs to recreate your scenario completely.

Thanks in advance!

Like I said, I wasn't sure how to post my example besides a screenshot.

See attached example.

Golf habdicap per hole.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	17	5	11	9	15	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	1	0	0	1	0	0	0	1	0		0	0	0	0	1	0	0	0	1
7		Score	4	5	3	4	4	4	2	5	5	36	5	4	3	4	6	4	3	3	4	36	72
8		Net	3	5	3	3	4	4	2	4	5	33	5	4	3	4	5	4	3	3	3	34	67
9
10	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
11		Score	5	4	4	6	6	5	3	7	5	45	5	4	4	6	7	4	4	4	5	43	88
12		Net	4	3	3	5	5	4	2	6	4	36	4	3	3	5	6	3	3	3	4	34	70
13
14	Golfer4	24	2	1	1	2	1	1	1	2	1		1	1	1	1	2	1	2	1	2
15		Score	5	4	5	7	5	6	4	8	5	49	6	5	4	4	7	6	5	3	6	46	95
16		Net	3	3	4	5	4	5	3	6	4	37	5	4	3	3	5	5	3	2	4	34	71
Sheet3

Cell Formulas
Range	Formula
L4,V15:V16,L15:L16,V11:V12,L11:L12,V7:V8,L7:L8,V4	L4	=SUM(C4:K4)
W4,W15:W16,W11:W12,W7:W8	W4	=L4+V4
C6:K6,M14:U14,C14:K14,M10:U10,C10:K10,M6:U6	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
C8:K8,M8:U8,C16:K16,M16:U16,C12:K12,M12:U12	C8	=C7-C6

I will give this a shot.

and keep in mind, if you are doing a trip, you will probably play different courses. In which case the indexes will change each time.

Luckily on this trip it's all the same course. But adding courses is simple once I get the initial setup done.

Here is what I currently have. I want the holes with 2 strokes to have ** in the cell.

2024 - Scorecard.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP
8							1			2			3			4			5			6			7			8			9			Out
9		White Tee					448			335			282			376			153			323			150			450			343			2863
10		Par					5			4			4			4			3			4			3			5			4			36
11		Hole HDCP					11			7			15			1			9			3			17			13			5
12		Haney						*			*						*			*			*						*			*
13
14
15		Crawford						*			*			*			*			*			*			*			*			*
16
17
18		Grogg						*			*			*			*			*			*			*			*			*
19
20
21		Beamer						*			*			*			*			*			*			*			*			*
22
23
24		Team Score					Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net
25
26
27
28							10			11			12			13			14			15			16			17			18			In		Total		Playing Handicap		Net Score
29		White Tee					365			334			465			354			172			362			163			342			512			3069		5932
30		Par					4			4			5			4			3			4			3			4			5			36		72
31		Hole HDCP					2			18			8			12			14			10			4			16			6
32		Haney						*						*			*			*			*			*						*						14
33
34
35		Crawford						*						*			*			*			*			*			*			*						17
36
37
38		Grogg						*			*			*			*			*			*			*			*			*						18
39
40
41		Beamer						*			*			*			*			*			*			*			*			*						19
42
43
44		Team Score					Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net		Gross	Net	Gross
45
46
47
Sheet 1

Cell Formulas
Range	Formula
AH10,AH30	AH10	=SUM(G10:AG10)
G12	G12	=IFS(G11>AL32,"*",AL32<18," ")
H12,AF32,AC32,Z32,W32,T32,Q32,N32,K32,H32,AF12,AC12,Z12,W12,T12,Q12,N12,K12	H12	=IF(G11>$AL$32," ","*")
H15,AF35,AC35,Z35,W35,T35,Q35,N35,K35,H35,AF15,AC15,Z15,W15,T15,Q15,N15,K15	H15	=IF(G11>$AL$35," ","*")
H18,AF38,AC38,Z38,W38,T38,Q38,N38,K38,H38,AF18,AC18,Z18,W18,T18,Q18,N18,K18	H18	=IF(G11>$AL$38," ","*")
H21,AF41,AC41,Z41,W41,T41,Q41,N41,K41,H41,AF21,AC21,Z21,W21,T21,Q21,N21,K21	H21	=IF(G11>$AL$41," ","*")
AJ29:AJ30	AJ29	=SUM(AH9,AH29)
B32,B35,B38,B41	B32	=B12

 

[mikewohlwend]

See attached example.

Golf habdicap per hole.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	17	5	11	9	15	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	1	0	0	1	0	0	0	1	0		0	0	0	0	1	0	0	0	1
7		Score	4	5	3	4	4	4	2	5	5	36	5	4	3	4	6	4	3	3	4	36	72
8		Net	3	5	3	3	4	4	2	4	5	33	5	4	3	4	5	4	3	3	3	34	67
9
10	Golfer3	18	1	1	1	1	1	1	1	1	1		1	1	1	1	1	1	1	1	1
11		Score	5	4	4	6	6	5	3	7	5	45	5	4	4	6	7	4	4	4	5	43	88
12		Net	4	3	3	5	5	4	2	6	4	36	4	3	3	5	6	3	3	3	4	34	70
13
14	Golfer4	24	2	1	1	2	1	1	1	2	1		1	1	1	1	2	1	2	1	2
15		Score	5	4	5	7	5	6	4	8	5	49	6	5	4	4	7	6	5	3	6	46	95
16		Net	3	3	4	5	4	5	3	6	4	37	5	4	3	3	5	5	3	2	4	34	71
Sheet3

Cell Formulas
Range	Formula
L4,V15:V16,L15:L16,V11:V12,L11:L12,V7:V8,L7:L8,V4	L4	=SUM(C4:K4)
W4,W15:W16,W11:W12,W7:W8	W4	=L4+V4
C6:K6,M14:U14,C14:K14,M10:U10,C10:K10,M6:U6	C6	=INT($B6/18)+IF(C$3<=MOD($B6,18),1,0)
C8:K8,M8:U8,C16:K16,M16:U16,C12:K12,M12:U12	C8	=C7-C6

If I could get this formula to show asterisk or dots it would be perfect.

 

[AhoyNC]

Try changing the formula to:
CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**")

You will need to change the hole Hndpc and Par to match your course.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	17	5	11	9	15	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	*			*				*							*				*
7
8	Golfer3	18	*	*	*	*	*	*	*	*	*		*	*	*	*	*	*	*	*	*
9
10	Golfer4	24	**	*	*	**	*	*	*	**	*		*	*	*	*	**	*	**	*	**
Sheet1

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K6,M10:U10,C10:K10,M8:U8,C8:K8,M6:U6	C6	=CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**")

 

[mikewohlwend]

Try changing the formula to:
CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**")

You will need to change the hole Hndpc and Par to match your course.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Course
2	Hole		1	2	3	4	5	6	7	8	9	Out	10	11	12	13	14	15	16	17	18	In	Total
3	Hcp.		3	13	17	5	11	9	15	1	7		10	12	16	14	2	8	6	18	4
4	Par		4	4	3	5	4	4	3	5	4	36	4	4	3	4	5	4	4	3	4	35	71
5	Name	Hndcp
6	Golfer2	5	*			*				*							*				*
7
8	Golfer3	18	*	*	*	*	*	*	*	*	*		*	*	*	*	*	*	*	*	*
9
10	Golfer4	24	**	*	*	**	*	*	*	**	*		*	*	*	*	**	*	**	*	**
Sheet1

Cell Formulas
Range	Formula
L4,V4	L4	=SUM(C4:K4)
W4	W4	=L4+V4
C6:K6,M10:U10,C10:K10,M8:U8,C8:K8,M6:U6	C6	=CHOOSE(INT($B6/18)+IF(C$3<=MOD($B6,18),2,1),"","*","**")

I will try that out.

What happens with your formula if a par 3 falls into the hole hdcp range? You aren't supposed to get more than 1 stroke on par 3's

 

[AhoyNC]

Unless you have a golfer with a 33 or higher handicap you should not see more than 1 stroke on a par 3. Most par 3's have the higher handicap ratings (at least on the courses I've played).

 

[mikewohlwend]

Unless you have a golfer with a 33 or higher handicap you should not see more than 1 stroke on a par 3. Most par 3's have the higher handicap ratings (at least on the courses I've played).

As you can see in the example I posted, which is the course we played this past weekend.
If you play 1-18 they have 4 par 3's ranked 4th, 9th, 14th and 17th. But they also have 27 holes so I added all the combinations.

If you play 10-27 they have 5 par 3's ranked 3rd, 10th, 12th, 13th and 18th.
If you play 1-9 & 19-27 they have 5 par 3's ranked 9th, 10th, 11th 17th and 18th.