Adjust Golf league Skins Conditional formatting

[Gulfcourse]

Hello,
I have a sheet, within a file, I'm using to find skins, the lowest individual score on each golf hole, if there is one.
I'm using conditional formatting and the formula:
=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,-C$1)
to highlight the cell if there is a skin on each hole throught for all 18 holes.

The person in ROW 17 has decided not to participate in the skins game.
I no longer wish to include his scores for skins but cannot simply cut the row since all the sheets list the golfers in alphabetical order, and I also use the sheet for points.
Another reason I don't want to cut the row, I need to keep it alphabetical.

This may happen for a few more people, perhaps row 24, so I may as well ask now how to do it since I've come up empty.

Is there a way to edit the formula to ignore certain rows?

Thank you

 

[arun_eagertolearn]

Hi

Please add an index column (with 1s and 0s) towards the end
That would have 1 for the contestants who continue, and 0s for those who drop out

You could create one more sheet (Eg: named 'Dummy'), with values of that sheet being Skin Sheet x index column values

Thus, the Dummy sheet would have 0s for those who have dropped out

The same formulae/conditional formatting could be applied on the new sheet to get the desired results

 

[Gulfcourse]

Hi

Please add an index column (with 1s and 0s) towards the end
That would have 1 for the contestants who continue, and 0s for those who drop out

You could create one more sheet (Eg: named 'Dummy'), with values of that sheet being Skin Sheet x index column values

Thus, the Dummy sheet would have 0s for those who have dropped out

The same formulae/conditional formatting could be applied on the new sheet to get the desired results

Hello, I really appreciate the reply and I know enough just to be dangerous!
I copied the sheet to a new workbook and created an Index in column Y with 1's and 0's.
As instructed, I copied that sheet to a new one called Dummy, but I'm the dummy that doesn't know how to add column Y, to look for only 1's, to the conditional formatting formula.

I got lost when you wrote "with values of that sheet being Skin Sheet x index column values"
this is the current formula:
=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,-C$1)
Where do I add if column Y = 1?
Thank you!

 

[JGordon11]

=AND(C4=MINIFS(C$4:C$36,C$4:C$36,">0"),COUNTIFS(C$4:C$36,C4)=1,-C$1, $Y4=1)

 

[Gulfcourse]

Thank you, Thank you, Thank you!

 

[arun_eagertolearn]

The method suggested by @JGordon11 is definitely the simplest one

 

[Gulfcourse]

Conditional formating not working?
Lincoln in P15 and Zeman in P22 both had a 4 on Hole #11 but Lincoln is not in the game, 0 in AA15, but the 4 for Zeman is not being shaded?
This is the current formula
=AND(E4=MINIFS(E$4:E$28,E$4:E$28,">0"),COUNTIFS(E$4:E$28,E4)=1,-E$1, $AA4=1)
I can't figure it out, THANK YOU!

GMGOLF_NEW.xlsx
	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
1		Hole	1	2	3	4	5	6	7	8	9	OUT	10	11	12	13	14	15	16	17	18	IN	GROSS	Points
2		PAR	4	3	5	4	4	3	4	4	4	35	5	5	3	4	4	4	4	3	4	36	71	36
3	PLAYER	HNDCP										F										B	SCORE	POINTS	Index
4	Bonham	12.3	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
5	Dangerfield	13.4	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
6	Edmisten	9.9	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
7	Frederick	8	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
8	Funari	11	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
9	Gosnell	17.8	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
10	Gressell	12.9	6	5	6	5	6	4	4	4	6	46	6	6	4	5	6	5	5	5	4	46	92	15	1
11	Hibjan	8.5	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
12	Hobbs	10.8	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
13	Keep	12.3	5	3	5	4	5	3	6	4	5	40	5	6	4	5	6	5	5	4	6	46	86	21	1
14	Kilroy	9.6	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
15	Lincoln	16.7	5	5	5	4	6	4	4	6	5	44	6	4	3	4	6	5	5	4	6	43	87	21	0
16	Nassar	18.7	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
17	Petro	14.4	5	4	6	6	5	4	5	6	6	47	7	6	3	5	6	4	6	3	5	45	92	15	1
18	Roderick	5.7	4	4	4	4	5	4	3	5	6	39	6	5	5	4	5	5	5	3	4	42	81	28	1
19	Sepic	13.1	5	3	5	6	5	4	5	5	6	44	7	5	5	5	6	6	6	4	5	49	93	14	1
20	Theberge	8.6	0	0	0	0	0	0	0	0	0		0	0	0	0	0	0	0	0	0				1
21	Wallon	16.1	5	3	5	4	5	3	6	5	5	41	5	6	5	6	6	6	6	5	4	49	90	17	1
22	Zeman	15.8	5	3	7	6	5	5	4	5	5	45	7	4	3	4	6	6	5	5	5	45	90	18	1
SKINS

Cell Formulas
Range	Formula
E2:M2,O2:W2	E2	='18-hole scores'!D4
N2,X2	N2	=SUM('18-hole scores'!M4)
Y2	Y2	=SUM(N2+X2)
D4:D22	D4	=HANDICAP!D5
E4:Y4	E4	=('18-hole scores'!D6)
E5:Y5	E5	=('18-hole scores'!D8)
E6:Y6	E6	=('18-hole scores'!D10)
E7:Y7	E7	=('18-hole scores'!D12)
E8:Y8	E8	=('18-hole scores'!D14)
E9:N9,X9:Y9	E9	=('18-hole scores'!D16)
O9:W9	O9	=SUM('18-hole scores'!N16)
E10:Y10	E10	=('18-hole scores'!D18)
E11:Y11	E11	=('18-hole scores'!D20)
E12:Y12	E12	=('18-hole scores'!D22)
E13:Y13	E13	=('18-hole scores'!D24)
E14:Y14	E14	=('18-hole scores'!D26)
Z4	Z4	='18-hole scores'!AA7
Z5	Z5	='18-hole scores'!AA9
Z6	Z6	='18-hole scores'!AA11
Z7	Z7	='18-hole scores'!AA13
Z8	Z8	='18-hole scores'!AA15
Z9	Z9	='18-hole scores'!AA17
Z10	Z10	='18-hole scores'!AA19
Z11	Z11	='18-hole scores'!AA21
Z12	Z12	='18-hole scores'!AA23
Z13	Z13	='18-hole scores'!AA25
Z14	Z14	='18-hole scores'!AA27
Q15:Y15,E15:O15	Q15	=('18-hole scores'!P28)
Z15	Z15	='18-hole scores'!AA29
E16:Y16	E16	=('18-hole scores'!D30)
Z16	Z16	='18-hole scores'!AA31
E17:Y17	E17	=('18-hole scores'!D32)
Z17	Z17	='18-hole scores'!AA33
E18:Y18	E18	=('18-hole scores'!D34)
Z18	Z18	='18-hole scores'!AA35
E19:Y19	E19	=('18-hole scores'!D36)
Z19	Z19	='18-hole scores'!AA37
E20:Y20	E20	=('18-hole scores'!D38)
Z20	Z20	='18-hole scores'!AA39
E21:Y21	E21	=('18-hole scores'!D40)
Z21	Z21	='18-hole scores'!AA41
E22:N22,X22:Y22	E22	=('18-hole scores'!D42)
O22:W22	O22	=SUM('18-hole scores'!N42)
Z22	Z22	='18-hole scores'!AA43

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:W28	Expression	=AND(E4=MINIFS(E$4:E$28,E$4:E$28,">0"),COUNTIFS(E$4:E$28,E4)=1,-E$1, $AA4=1)	text	NO

 

[Fluff]

How about

=AND(E4=MINIFS(E$4:E$28,E$4:E$28,">0"),COUNTIFS(E$4:E$28,E4,$AA$4:$AA$28,1)=1,-E$1, $AA4=1)

 

[JGordon11]

=LET(e, E$4:E$28, aa, $AA$4:$AA$28, eaa, e*aa, low, MIN(IF(eaa=0,99,e)), ct, ROWS(FILTER(eaa, eaa=low)),(E4=low)*(ct=1)*$AA4)

 

[Gulfcourse]

Thank you I haven't had a chance to try either yet but will do it this evening!