Why does this appear as #NAME? on this page when it properly puts values on other pages?

[Tomeegee]

MrExcel helped me set up this page a couple of years ago for when my tournament had a woman join and I needed to populate the proper handicap on the scoring set up. The page properly distribute values to the pages it needs to, but when I need to add new courses here, I can't understand why it returns #NAME? what it is. This page is set up to show values if a Player is a woman "Gina M" or just a "Player". I don't get why all courses return #NAME? in the "if with Player" row for all courses and FALSE in the "if With Gina M" row when PineLakes is chosen. In the sheets that draw from this page, the values appear without these NAME and FALSE returns, but I don't know why they happen here. Arrays are way above my pay grade at the moment, so figuring this one out has been burdensome. Thanks for your help.

problem formula.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
1
2		Player
3		Tom G	M				Golf Course:		PineLakes
4
5		FRONT
6
7		HOLE	1	2	3	4	5	6	7	8	9	Total
8		Score Card Par	5	4	3	4	4	3	5	4	4	36
9		Hole Handicap	9	13	15	7	3	17	1	11	5
10
11			1	2	3	4	5	6	7	8	9		10	11	12	13	14	15	16	17	18
12
13		PineLakes	5	4	3	4	4	3	5	4	4	36	4	5	4	4	3	5	4	3	4	36	72
14		Men's Handicap	9	13	15	7	3	17	1	11	5		8	2	10	12	18	4	16	14	6
15		Ladies Handicap	3	7	15	9	17	13	5	11	1		18	6	14	10	12	4	16	8	2
16
17		Oleander	4	4	3	5	4	5	4	3	4	36	4	5	4	3	4	5	3	4	4	36	72
18		Men's Handicap	13	5	15	1	3	7	11	17	9		10	4	2	16	12	8	18	14	6
19		Ladies Handicap	13	5	15	1	3	7	11	17	9		10	4	2	16	12	8	18	14	6
20
21		IndianMound	4	3	5	4	4	3	5	4	4	36	5	4	3	4	4	4	3	5	4	36	72
22		Men's Handicap	11	17	1	13	7	15	3	9	5		4	14	18	6	12	10	16	2	8
23		Ladies Handicap	5	15	1	13	11	17	3	9	7		4	14	18	6	12	10	16	2	8
24
25		Sanctuary	4	4	5	3	4	3	4	5	4	36	4	4	4	3	4	5	3	5	4	36	72
26		Men's Handicap	3	13	15	11	7	17	9	5	1		14	6	10	18	4	12	16	8	2
27		Ladies Handicap	1	7	11	17	5	9	13	15	3		14	6	12	18	8	10	16	2	4
28
29
30		If with Player	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?	#NAME?		{=IF(AND(B3=Player,$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))}
31		if With Gina M	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE		{=IF(AND(B3="Gina M",$B5="FRONT",$I3="PineLakes"),$C$15:$K$15,IF(AND($B5="BACK",$I3="PineLakes"),$M$15:$U$15,IF(AND($B5="FRONT",$I3="Oleander"),$C$19:$K$19,IF(AND($B5="BACK",$I3="Oleander"),$M$19:$U$19,IF(AND($B5="FRONT",$I3="IndianMound"),$C$23:$K$23,IF(AND($B5="BACK",$I3="IndianMound"),$M$23:$U$23,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$27:$K$27,IF(AND($B5="BACK",$I3="Sanctuary"),$M$27:$U$27))))))))}
32
33		Comparison	9	13	15	7	3	17	1	11	5		=@INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
34
35
ParHCPs

Cell Formulas
Range	Formula
C3	C3	=IF(B3="Gina M","F","M")
C7:K7	C7	=IF(B5="FRONT",C11:K11,IF(B5="BACK",M11:U11))
C8:K8	C8	=INDEX(INDIRECT($I$3),1,IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
L8,V25,L25,V21,L21,V17,L17,V13,L13	L8	=SUM(C8:K8)
C9:K9	C9	=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
W13,W25,W21,W17	W13	=SUM(L13+V13)
C30:K30	C30	=IF(AND(B3=Player,$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))
C31:K31	C31	=IF(AND(B3="Gina M",$B5="FRONT",$I3="PineLakes"),$C$15:$K$15,IF(AND($B5="BACK",$I3="PineLakes"),$M$15:$U$15,IF(AND($B5="FRONT",$I3="Oleander"),$C$19:$K$19,IF(AND($B5="BACK",$I3="Oleander"),$M$19:$U$19,IF(AND($B5="FRONT",$I3="IndianMound"),$C$23:$K$23,IF(AND($B5="BACK",$I3="IndianMound"),$M$23:$U$23,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$27:$K$27,IF(AND($B5="BACK",$I3="Sanctuary"),$M$27:$U$27))))))))
M30:M31,M33	M30	=FORMULATEXT(C30)
C33:K33	C33	=INDEX(INDIRECT($I$3),IF($C$3="M",2,3),IF($B$5="FRONT",COLUMN(B1),COLUMN(L1)))
Press CTRL+SHIFT+ENTER to enter array formulas.
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
B3	List	=S22_90!$J$2:$J$19
B5	List	=Side
B13	List	=S22_90!$O$2:$O$6
B17	List	=S22_90!$O$2:$O$6
B21	List	=S22_90!$O$2:$O$6
B25	List	=S22_90!$O$2:$O$6
I3	List	=S22_90!$O$2:$O$6

 

[6StringJazzer]

Your formula refers to the named range Player. However, although you provided a copy of your sheet, we cannot see how this range is defined in your workbook, or whether it is defined at all. If it is not defined at all, you will get a #NAME error.

Go to Formulas > Name Manager and see if the name Player is defined. If it is not, that is the problem, but I don't have enough of your file to figure out how it's supposed to be defined.

 

[Peter_SSs]

I think that it is just that you have omitted inverted commas in the row 30 formula.

Row 31 starts with: =IF(AND(B3="Gina M",
Row 30 starts with: =IF(AND(B3=Player,
but shouldn't it be: =IF(AND(B3="Player",

Actually, I think that I am changing my mind & it is possibly more likely if you mean B3 is any player except Gina M then
=IF(AND(B3<>"Gina M",
Also, given that you have Excel 365 the formula in C30 does not need to be entered with Ctrl+Shift+Enter or entered in D30:K30 - see C30 below.

If I am understanding what you are trying to do, then I have suggested a much simpler formula in cell C31 that should work whether it is Gina M or a male player in B3 and also whether it if FRONT or BACK in B5

23 08 27.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2	Player
3	Tom G	M				Golf Course:		Oleander
4
5	FRONT
6
7	HOLE	1	2	3	4	5	6	7	8	9
10
11		1	2	3	4	5	6	7	8	9
12
13	PineLakes	5	4	3	4	4	3	5	4	4
14	Men's Handicap	9	13	15	7	3	17	1	11	5
15	Ladies Handicap	3	7	15	9	17	13	5	11	1
16
17	Oleander	4	4	3	5	4	5	4	3	4
18	Men's Handicap	13	5	15	1	3	7	11	17	9
19	Ladies Handicap	15	5	13	1	9	7	11	17	3
20
28
29
30	If with Player	13	5	15	1	3	7	11	17	9
31	If with Player or Gina M	13	5	15	1	3	7	11	17	9
Sheet2 (2)

Cell Formulas
Range	Formula
C3	C3	=IF(B3="Gina M","F","M")
C7:K7	C7	=IF(B5="FRONT",C11:K11,IF(B5="BACK",M11:U11))
C30:K30	C30	=IF(AND(B3<>"Gina M",$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))
C31:K31	C31	=INDEX(C14:U27,MATCH(I3,B13:B26,0)+(C3="F"),SEQUENCE(,9,IF(B5="FRONT",1,11)))
Dynamic array formulas.

 

[Tomeegee]

I think that it is just that you have omitted inverted commas in the row 30 formula.

Row 31 starts with: =IF(AND(B3="Gina M",
Row 30 starts with: =IF(AND(B3=Player,
but shouldn't it be: =IF(AND(B3="Player",

Actually, I think that I am changing my mind & it is possibly more likely if you mean B3 is any player except Gina M then
=IF(AND(B3<>"Gina M",
Also, given that you have Excel 365 the formula in C30 does not need to be entered with Ctrl+Shift+Enter or entered in D30:K30 - see C30 below.

If I am understanding what you are trying to do, then I have suggested a much simpler formula in cell C31 that should work whether it is Gina M or a male player in B3 and also whether it if FRONT or BACK in B5

23 08 27.xlsm
	B	C	D	E	F	G	H	I	J	K
1
2	Player
3	Tom G	M				Golf Course:		Oleander
4
5	FRONT
6
7	HOLE	1	2	3	4	5	6	7	8	9
10
11		1	2	3	4	5	6	7	8	9
12
13	PineLakes	5	4	3	4	4	3	5	4	4
14	Men's Handicap	9	13	15	7	3	17	1	11	5
15	Ladies Handicap	3	7	15	9	17	13	5	11	1
16
17	Oleander	4	4	3	5	4	5	4	3	4
18	Men's Handicap	13	5	15	1	3	7	11	17	9
19	Ladies Handicap	15	5	13	1	9	7	11	17	3
20
28
29
30	If with Player	13	5	15	1	3	7	11	17	9
31	If with Player or Gina M	13	5	15	1	3	7	11	17	9
Sheet2 (2)

Cell Formulas
Range	Formula
C3	C3	=IF(B3="Gina M","F","M")
C7:K7	C7	=IF(B5="FRONT",C11:K11,IF(B5="BACK",M11:U11))
C30:K30	C30	=IF(AND(B3<>"Gina M",$B5="FRONT",$I3="PineLakes"),$C$14:$K$14,IF(AND($B5="BACK",$I3="PineLakes"),$M$14:$U$14,IF(AND($B5="FRONT",$I3="Oleander"),$C$18:$K$18,IF(AND($B5="BACK",$I3="Oleander"),$M$18:$U$18,IF(AND($B5="FRONT",$I3="IndianMound"),$C$22:$K$22,IF(AND($B5="BACK",$I3="IndianMound"),$M$22:$U$22,IF(AND($B5="FRONT",$I3="Sanctuary"),$C$26:$K$26,IF(AND($B5="BACK",$I3="Sanctuary"),$M$26:$U$26))))))))
C31:K31	C31	=INDEX(C14:U27,MATCH(I3,B13:B26,0)+(C3="F"),SEQUENCE(,9,IF(B5="FRONT",1,11)))
Dynamic array formulas.

Thank you SO much. That works like a charm and it frees me from those cumbersome arrays! MrExcel is the best

 

[Peter_SSs]

Thank you SO much. That works like a charm and it frees me from those cumbersome arrays!

You're welcome. Thanks for the follow-up.

 

[6StringJazzer]

Thank you SO much. That works like a charm and it frees me from those cumbersome arrays!

Keep in mind that they are still array formulas. It's just that 365 does not require to you do anything special to make them so; it is able to determine it from context. The only thing you are freed from is having to hit CTRL+SHIFT+ENTER and the brackets. But do be aware of possible performance issues whenever you use array formulas.