I having some problems with my pick them section my file

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
16NFL GAMES PICK THEM2021HOME
2GAMESROAD TEAM PICK ROAD TEAMSROAD SCORESVSHOME TEAM PICKHOME TEAMSHOME TEAM SCORESGAMECORECT/INCORRECTHOME TEAM OPTION BUTTON VALUEHOME TEAM W/L
31BUCCANEERS0VSEAGLES0GOT RIGHT01  L
42DOLPHINS0VSJAGUARS0GOT RIGHT %0%2  L
53VINKINGS VSPANTHERS03I L
64PACKERS0VSBEARS0GOT WRONG24  L
75TEXANS0VSCOLTS0GOT WRONG%100%5  L
86BENGALS0VSLIONS06  L
97RAMS0VSGIANTS0BYE WEEKSTEAMS7  L
108CHARGERS0VSRAVENS01FALCONS8  L
119CHIEFS0VSREDSKINS02SAINTS9  L
1210CARDINALS0VSBROWNS03JETS10  L
1311RAIDERS0VSBRONCOS0449ERS11  L
1412COWBOYS0VSPATRIOTS05 12  L
1513SEAHAWKS0VSSTELLERS 6 13I W
1614BILLS0VSTITANS014  L
1715BYE WEEK TEAM VSBYE WEEK TEAM 15   
1816BYE WEEK TEAM VSBYE WEEK TEAM 16   
PICK THEM WEEK 6
Cell Formulas
RangeFormula
H1H1=YEAR(TODAY())
C3:C18C3=IF('6'!D2<>"",'6'!D2,"BYE WEEK TEAM")
D3:D18,H3:H18D3=IFERROR(VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,7,0),"")
G3:G18G3=IF('6'!G2<>"",'6'!G2,"BYE WEEK TEAM")
O3:O18O3=IF(Q2="","",IF(D3=H3,"",IF(AND(P3="T",Q3="W"),"C",IF(AND(P3="F",Q3="L"),"C","I"))))
P3:P18P3=IF(AND(F3="*",B3=""),"T",IF(AND(F3="",B3="*"),"F",""))
Q3:Q18Q3=IF(AND(D3="",H3=""),"",IF(H3>D3,"W","L"))
L3L3=COUNTIF($O$2:$O$18,"C")
L4L4=IFERROR(L3/(L3+L6),"")
L6L6=COUNTIF($O$2:$O$18,"I")
L7L7=IFERROR(L6/(L3+L6),"")
L10:L15L10=IF('BYE WEEKS'!Q3<>"",'BYE WEEKS'!Q3,"")
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,285
Office Version
  1. 365
Platform
  1. Windows
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)
Excel Formula:
=IFERROR(N(VLOOKUP(C3,INPUT_SCORES!$A$2:$R$33,7,0)),"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Nov 26, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Glad we could help & thanks for the feedback.
 

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
98
Office Version
  1. 365
Platform
  1. Windows
DOWNLOAD UPDATED NFL SCHEDULE FILE

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


VBA Code:
'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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,938
Office Version
  1. 365
Platform
  1. Windows
As this is a totally different question, it needs a new thread. Thanks
 

Forum statistics

Threads
1,148,179
Messages
5,745,201
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top