I having some problems with my pick them section my file

TLS49

Board Regular
Joined
Nov 26, 2019
Messages
135
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

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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)),"")
 
Upvote 0
Looks like you have a couple of typos in the team names, I'd guess that Vinkings should be Vikings & Stellers should be Steelers
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
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,"")
 
Upvote 0
As this is a totally different question, it needs a new thread. Thanks
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,027
Members
452,374
Latest member
keccles

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
Back
Top