I having some problems with my pick them section my file

[TLS49]

DOWNLOAD NFL SCHEDULE FILE

I have some problem with my pick them week 1-18.

1. Some of scores in Columns D & H is not showing up. The score is looking a form Input_scores worksheet.

2. in some worksheets have L3 & L6 is having number in cell. should have 0 when are no score in columns D & H

3.* to pick team I think going wiill win look up score in D & H. The scores in input_scores worksheet

color in green is i got right, red in green is i got wrong. light Blue is no * on the worksheet, after I put a * in column D or H will be normal it is using conditional formatting.

4.L10:L15 if you teams in this section will not bein columns C & G will put Bye Team in columns C or G at bottom of list. sample like Pick Them week 6

5. O3:O18 is helper section some worksheets have C or I in cell should not be in cells until the scores is inputed.

NFL SCHEDULE.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	6		NFL GAMES PICK THEM					2021			HOME
2	GAMES	ROAD TEAM PICK	ROAD TEAMS	ROAD SCORES	VS	HOME TEAM PICK	HOME TEAMS	HOME TEAM SCORES						GAME	CORECT/INCORRECT	HOME TEAM OPTION BUTTON VALUE	HOME TEAM W/L
3	1		BUCCANEERS	0	VS		EAGLES	0			GOT RIGHT	0		1			L
4	2		DOLPHINS	0	VS		JAGUARS	0			GOT RIGHT %	0%		2			L
5	3		VINKINGS		VS		PANTHERS	0						3	I		L
6	4		PACKERS	0	VS		BEARS	0			GOT WRONG	2		4			L
7	5		TEXANS	0	VS		COLTS	0			GOT WRONG%	100%		5			L
8	6		BENGALS	0	VS		LIONS	0						6			L
9	7		RAMS	0	VS		GIANTS	0			BYE WEEKS	TEAMS		7			L
10	8		CHARGERS	0	VS		RAVENS	0			1	FALCONS		8			L
11	9		CHIEFS	0	VS		REDSKINS	0			2	SAINTS		9			L
12	10		CARDINALS	0	VS		BROWNS	0			3	JETS		10			L
13	11		RAIDERS	0	VS		BRONCOS	0			4	49ERS		11			L
14	12		COWBOYS	0	VS		PATRIOTS	0			5			12			L
15	13		SEAHAWKS	0	VS		STELLERS				6			13	I		W
16	14		BILLS	0	VS		TITANS	0						14			L
17	15		BYE WEEK TEAM		VS		BYE WEEK TEAM							15
18	16		BYE WEEK TEAM		VS		BYE WEEK TEAM							16
PICK THEM WEEK 6

Cell Formulas
Range	Formula
H1	H1	=YEAR(TODAY())
C3:C18	C3	=IF('6'!D2<>"",'6'!D2,"BYE WEEK TEAM")
D3:D18,H3:H18	D3	=IFERROR(VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,7,0),"")
G3:G18	G3	=IF('6'!G2<>"",'6'!G2,"BYE WEEK TEAM")
O3:O18	O3	=IF(Q2="","",IF(D3=H3,"",IF(AND(P3="T",Q3="W"),"C",IF(AND(P3="F",Q3="L"),"C","I"))))
P3:P18	P3	=IF(AND(F3="*",B3=""),"T",IF(AND(F3="",B3="*"),"F",""))
Q3:Q18	Q3	=IF(AND(D3="",H3=""),"",IF(H3>D3,"W","L"))
L3	L3	=COUNTIF($O$2:$O$18,"C")
L4	L4	=IFERROR(L3/(L3+L6),"")
L6	L6	=COUNTIF($O$2:$O$18,"I")
L7	L7	=IFERROR(L6/(L3+L6),"")
L10:L15	L10	=IF('BYE WEEKS'!Q3<>"",'BYE WEEKS'!Q3,"")

 

[Alex Blakenburg]

Does sheet "6" by any change have a formula in column 7 ?
At the moment the formula if it finds the lookup value will return the value in column 7.
If this is blank it will return 0 but if it is "" then it will return blank.
If it can't find it at all it will return 0 by virtue of the IFERROR.

The cleanest way of fixing it would be to change your formula in sheet "6" to use 0 and not "" in the formula.
To fix it in your formula you would either need to use the vlookup twice in and If statement or switch to a different formula that treats a result of "" the same as not found (ie filters it out)
Actually you could change your formula to this, which should work.
(wrapped your vlookup in the N() function which converts text (which includes "") to 0)

=IFERROR(N(VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,7,0)),"")

 

[Fluff]

Looks like you have a couple of typos in the team names, I'd guess that Vinkings should be Vikings & Stellers should be Steelers

 

[TLS49]

Looks like you have a couple of typos in the team names, I'd guess that Vinkings should be Vikings & Stellers should be Steelers

Thanks You Fluff That fix the problems I was having for now.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[TLS49]

DOWNLOAD UPDATED NFL SCHEDULE FILE

How convert this formulas in VBA Codes with notes for each section

'ROAD TEAMS NAMES C3:C18        
=IF('1'!D2<>"",'1'!D2,"BYE WEEK TEAM")
=IF('2'!D2<>"",'2'!D2,"BYE WEEK TEAM")
=IF('3'!D2<>"",'3'!D2,"BYE WEEK TEAM")
=IF('4'!D2<>"",'4'!D2,"BYE WEEK TEAM")
=IF('5'!D2<>"",'5'!D2,"BYE WEEK TEAM")
=IF('6'!D2<>"",'6'!D2,"BYE WEEK TEAM")
=IF('7'!D2<>"",'7'!D2,"BYE WEEK TEAM")
=IF('8'!D2<>"",'8'!D2,"BYE WEEK TEAM")
=IF('9'!D2<>"",'9'!D2,"BYE WEEK TEAM")
=IF('10'!D2<>"",'10'!D2,"BYE WEEK TEAM")
=IF('11'!D2<>"",'11'!D2,"BYE WEEK TEAM")
=IF('12'!D2<>"",'12'!D2,"BYE WEEK TEAM")
=IF('13'!D2<>"",'13'!D2,"BYE WEEK TEAM")
=IF('14'!D2<>"",'14'!D2,"BYE WEEK TEAM")
=IF('15'!D2<>"",'15'!D2,"BYE WEEK TEAM")
=IF('16'!D2<>"",'16'!D2,"BYE WEEK TEAM")
=IF('17'!D2<>"",'17'!D2,"BYE WEEK TEAM")
=IF('18'!D2<>"",'18'!D2,"BYE WEEK TEAM")

'HOME TEAMS NAMES G3:G18
=IF('1'!G2<>"",'1'!G2,"BYE WEEK TEAM")
=IF('2'!G2<>"",'2'!G2,"BYE WEEK TEAM")
=IF('3'!G2<>"",'3'!G2,"BYE WEEK TEAM")
=IF('4'!G2<>"",'4'!G2,"BYE WEEK TEAM")
=IF('5'!G2<>"",'5'!G2,"BYE WEEK TEAM")
=IF('6'!G2<>"",'6'!G2,"BYE WEEK TEAM")
=IF('7'!G2<>"",'7'!G2,"BYE WEEK TEAM")
=IF('8'!G2<>"",'8'!G2,"BYE WEEK TEAM")
=IF('9'!G2<>"",'9'!G2,"BYE WEEK TEAM")
=IF('10'!G2<>"",'10'!G2,"BYE WEEK TEAM")
=IF('11'!G2<>"",'11'!G2,"BYE WEEK TEAM")
=IF('12'!G2<>"",'12'!G2,"BYE WEEK TEAM")
=IF('13'!G2<>"",'13'!G2,"BYE WEEK TEAM")
=IF('14'!G2<>"",'14'!G2,"BYE WEEK TEAM")
=IF('15'!G2<>"",'15'!G2,"BYE WEEK TEAM")
=IF('16'!G2<>"",'16'!G2,"BYE WEEK TEAM")
=IF('17'!G2<>"",'17'!G2,"BYE WEEK TEAM")
=IF('18'!G2<>"",'18'!G2,"BYE WEEK TEAM")



'PICK THEM WEEK 1  L10:L15
=IF('BYE WEEKS'!B3<>"",'BYE WEEKS'!B3,"")
=IF('BYE WEEKS'!B4<>"",'BYE WEEKS'!B4,"")
=IF('BYE WEEKS'!B5<>"",'BYE WEEKS'!B5,"")
=IF('BYE WEEKS'!B6<>"",'BYE WEEKS'!B6,"")
=IF('BYE WEEKS'!B7<>"",'BYE WEEKS'!B7,"")
=IF('BYE WEEKS'!B8<>"",'BYE WEEKS'!B8,"")

'PICK THEM WEEK 2 L10:L15
=IF('BYE WEEKS'!E3<>"",'BYE WEEKS'!E3,"")
=IF('BYE WEEKS'!E4<>"",'BYE WEEKS'!E4,"")
=IF('BYE WEEKS'!E5<>"",'BYE WEEKS'!E5,"")
=IF('BYE WEEKS'!E6<>"",'BYE WEEKS'!E6,"")
=IF('BYE WEEKS'!E7<>"",'BYE WEEKS'!E7,"")
=IF('BYE WEEKS'!E8<>"",'BYE WEEKS'!E8,"")

'PICK THEM WEEK 3 L10:L15
=IF('BYE WEEKS'!H3<>"",'BYE WEEKS'!H3,"")
=IF('BYE WEEKS'!H4<>"",'BYE WEEKS'!H4,"")
=IF('BYE WEEKS'!H5<>"",'BYE WEEKS'!H5,"")
=IF('BYE WEEKS'!H6<>"",'BYE WEEKS'!H6,"")
=IF('BYE WEEKS'!H7<>"",'BYE WEEKS'!H7,"")
=IF('BYE WEEKS'!H8<>"",'BYE WEEKS'!H8,"")

'PICK THEM WEEK 4 L10:L15
=IF('BYE WEEKS'!K3<>"",'BYE WEEKS'!K3,"")
=IF('BYE WEEKS'!K4<>"",'BYE WEEKS'!K4,"")
=IF('BYE WEEKS'!K5<>"",'BYE WEEKS'!K5,"")
=IF('BYE WEEKS'!K6<>"",'BYE WEEKS'!K6,"")
=IF('BYE WEEKS'!K7<>"",'BYE WEEKS'!K7,"")
=IF('BYE WEEKS'!K8<>"",'BYE WEEKS'!K8,"")

'PICK THEM WEEK 5 L10:L15
=IF('BYE WEEKS'!N3<>"",'BYE WEEKS'!N3,"")
=IF('BYE WEEKS'!N4<>"",'BYE WEEKS'!N4,"")
=IF('BYE WEEKS'!N5<>"",'BYE WEEKS'!N5,"")
=IF('BYE WEEKS'!N6<>"",'BYE WEEKS'!N6,"")
=IF('BYE WEEKS'!N7<>"",'BYE WEEKS'!N7,"")
=IF('BYE WEEKS'!N8<>"",'BYE WEEKS'!N8,"")

'PICK THEM WEEK 6  L10:L15
=IF('BYE WEEKS'!Q3<>"",'BYE WEEKS'!Q3,"")
=IF('BYE WEEKS'!Q4<>"",'BYE WEEKS'!Q4,"")
=IF('BYE WEEKS'!Q5<>"",'BYE WEEKS'!Q5,"")
=IF('BYE WEEKS'!Q6<>"",'BYE WEEKS'!Q6,"")
=IF('BYE WEEKS'!Q7<>"",'BYE WEEKS'!Q7,"")
=IF('BYE WEEKS'!Q8<>"",'BYE WEEKS'!Q8,"")

'PICK THEM WEEK 7  L10:L15
=IF('BYE WEEKS'!B11<>"",'BYE WEEKS'!B11,"")
=IF('BYE WEEKS'!B12<>"",'BYE WEEKS'!B12,"")
=IF('BYE WEEKS'!B13<>"",'BYE WEEKS'!B13,"")
=IF('BYE WEEKS'!B14<>"",'BYE WEEKS'!B14,"")
=IF('BYE WEEKS'!B15<>"",'BYE WEEKS'!B15,"")
=IF('BYE WEEKS'!B16<>"",'BYE WEEKS'!B16,"")

'PICK THEM WEEK 8  L10:L15
=IF('BYE WEEKS'!E11<>"",'BYE WEEKS'!E11,"")
=IF('BYE WEEKS'!E12<>"",'BYE WEEKS'!E12,"")
=IF('BYE WEEKS'!E13<>"",'BYE WEEKS'!E13,"")
=IF('BYE WEEKS'!E14<>"",'BYE WEEKS'!E14,"")
=IF('BYE WEEKS'!E15<>"",'BYE WEEKS'!E15,"")
=IF('BYE WEEKS'!E16<>"",'BYE WEEKS'!E16,"")

'PICK THEM WEEK 9  L10:L15
=IF('BYE WEEKS'!H11<>"",'BYE WEEKS'!H11,"")
=IF('BYE WEEKS'!H12<>"",'BYE WEEKS'!H12,"")
=IF('BYE WEEKS'!H13<>"",'BYE WEEKS'!H13,"")
=IF('BYE WEEKS'!H14<>"",'BYE WEEKS'!H14,"")
=IF('BYE WEEKS'!H15<>"",'BYE WEEKS'!H15,"")
=IF('BYE WEEKS'!H16<>"",'BYE WEEKS'!H16,"")

'PICK THEM WEEK 10  L10:L15
=IF('BYE WEEKS'!K11<>"",'BYE WEEKS'!K11,"")
=IF('BYE WEEKS'!K12<>"",'BYE WEEKS'!K12,"")
=IF('BYE WEEKS'!K13<>"",'BYE WEEKS'!K13,"")
=IF('BYE WEEKS'!K14<>"",'BYE WEEKS'!K14,"")
=IF('BYE WEEKS'!K15<>"",'BYE WEEKS'!K15,"")
=IF('BYE WEEKS'!K16<>"",'BYE WEEKS'!K16,"")

'PICK THEM WEEK 11  L10:L15
=IF('BYE WEEKS'!N11<>"",'BYE WEEKS'!N11,"")
=IF('BYE WEEKS'!N12<>"",'BYE WEEKS'!N12,"")
=IF('BYE WEEKS'!N13<>"",'BYE WEEKS'!N13,"")
=IF('BYE WEEKS'!N14<>"",'BYE WEEKS'!N14,"")
=IF('BYE WEEKS'!N15<>"",'BYE WEEKS'!N15,"")
=IF('BYE WEEKS'!N16<>"",'BYE WEEKS'!N16,"")

'PICK THEM WEEK 12  L10:L15
=IF('BYE WEEKS'!Q11<>"",'BYE WEEKS'!Q11,"")
=IF('BYE WEEKS'!Q12<>"",'BYE WEEKS'!Q12,"")
=IF('BYE WEEKS'!Q13<>"",'BYE WEEKS'!Q13,"")
=IF('BYE WEEKS'!Q14<>"",'BYE WEEKS'!Q14,"")
=IF('BYE WEEKS'!Q15<>"",'BYE WEEKS'!Q15,"")
=IF('BYE WEEKS'!Q16<>"",'BYE WEEKS'!Q16,"")

'PICK THEM WEEK 13  L10:L15
=IF('BYE WEEKS'!B19<>"",'BYE WEEKS'!B19,"")
=IF('BYE WEEKS'!B20<>"",'BYE WEEKS'!B20,"")
=IF('BYE WEEKS'!B21<>"",'BYE WEEKS'!B21,"")
=IF('BYE WEEKS'!B22<>"",'BYE WEEKS'!B22,"")
=IF('BYE WEEKS'!B23<>"",'BYE WEEKS'!B23,"")
=IF('BYE WEEKS'!B24<>"",'BYE WEEKS'!B24,"")

'PICK THEM WEEK 14  L10:L15
=IF('BYE WEEKS'!E19<>"",'BYE WEEKS'!E19,"")
=IF('BYE WEEKS'!E20<>"",'BYE WEEKS'!E20,"")
=IF('BYE WEEKS'!E21<>"",'BYE WEEKS'!E21,"")
=IF('BYE WEEKS'!E22<>"",'BYE WEEKS'!E22,"")
=IF('BYE WEEKS'!E23<>"",'BYE WEEKS'!E23,"")
=IF('BYE WEEKS'!E24<>"",'BYE WEEKS'!E24,"")

'PICK THEM WEEK 15  L10:L15
=IF('BYE WEEKS'!H19<>"",'BYE WEEKS'!H19,"")
=IF('BYE WEEKS'!H20<>"",'BYE WEEKS'!H20,"")
=IF('BYE WEEKS'!H21<>"",'BYE WEEKS'!H21,"")
=IF('BYE WEEKS'!H22<>"",'BYE WEEKS'!H22,"")
=IF('BYE WEEKS'!H23<>"",'BYE WEEKS'!H23,"")
=IF('BYE WEEKS'!H24<>"",'BYE WEEKS'!H24,"")

'PICK THEM WEEK 16  L10:L15
=IF('BYE WEEKS'!K19<>"",'BYE WEEKS'!K19,"")
=IF('BYE WEEKS'!K20<>"",'BYE WEEKS'!K20,"")
=IF('BYE WEEKS'!K21<>"",'BYE WEEKS'!K21,"")
=IF('BYE WEEKS'!K22<>"",'BYE WEEKS'!K22,"")
=IF('BYE WEEKS'!K23<>"",'BYE WEEKS'!K23,"")
=IF('BYE WEEKS'!K24<>"",'BYE WEEKS'!K24,"")

'PICK THEM WEEK 17  L10:L15
=IF('BYE WEEKS'!N19<>"",'BYE WEEKS'!N19,"")
=IF('BYE WEEKS'!N20<>"",'BYE WEEKS'!N20,"")
=IF('BYE WEEKS'!N21<>"",'BYE WEEKS'!N21,"")
=IF('BYE WEEKS'!N22<>"",'BYE WEEKS'!N22,"")
=IF('BYE WEEKS'!N23<>"",'BYE WEEKS'!N23,"")
=IF('BYE WEEKS'!N24<>"",'BYE WEEKS'!N24,"")

'PICK THEM WEEK 18  L10:L15
=IF('BYE WEEKS'!Q19<>"",'BYE WEEKS'!Q19,"")
=IF('BYE WEEKS'!Q20<>"",'BYE WEEKS'!Q20,"")
=IF('BYE WEEKS'!Q21<>"",'BYE WEEKS'!Q21,"")
=IF('BYE WEEKS'!Q22<>"",'BYE WEEKS'!Q22,"")
=IF('BYE WEEKS'!Q23<>"",'BYE WEEKS'!Q23,"")
=IF('BYE WEEKS'!Q24<>"",'BYE WEEKS'!Q24,"")

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks