I need help with couple things clear data when you change validation and find monday night game teams

[TLS49]

I need help with two things one when you change F1. I want clear C2:C18 and G2:G18

I also need help getting name teams names in E 21 and E23 for Monday night teams

Book1
	A	B	C	D	E	F	G	H	I
1	WEEK					15
2	GAMES	DAYS	ROAD TEAM PICK	ROAD TEAMS	ROAD SCORES	VS	HOME TEAM PICK	HOME TEAMS	HOME TEAM SCORES
3	1	Thursday		JETS	21	VS		RAVENS	42
4	2	Sunday	*	SEAHAWKS	30	VS		PANTHERS	24
5	3	Sunday	*	PATRIOTS	34	VS		BENGALS	13
6	4	Sunday		BUCCANEERS	38	VS	*	LIONS	17
7	5	Sunday		BEARS	13	VS	*	PACKERS	21
8	6	Sunday		BRONCOS	3	VS	*	CHIEFS	23
9	7	Sunday		DOLPHINS	20	VS	*	GIANTS	36
10	8	Sunday	*	TEXANS	24	VS		TITANS	21
11	9	Sunday	*	BILLS	17	VS		STEELERS	10
12	10	Sunday	*	EAGLES	37	VS		REDSKINS	27
13	11	Sunday	*	BROWNS	24	VS		CARDINALS	38
14	12	Sunday		JAGUARS	20	VS	*	RAIDERS	16
15	13	Sunday		RAMS	21	VS	*	COWBOYS	44
16	14	Sunday		FALCONS	29	VS	*	49ERS	22
17	15	Sunday	*	VIKINGS	39	VS		CHARGERS	10
18	16	Monday		COLTS	7	VS	*	SAINTS	34
19
20				AVERAGES FOR MONDAY NIGHT TEAMS
21	MY MONDAY NIGHT GAME TOTAL POINTS			PUT ROAD TEAM HERE MONDAY NIGHT	#N/A			I need help with two things one when you change F1. I want clear C2:C18 and G2:G18
22
23	MONDAY NIGHT TOTAL POINTS			PUT HOME TEAM HERE MONDAY NIGHT	#N/A			I also need help getting name teams names in E 21 and E23 for Monday night teams
24
25	DIFFERENCE			TOTAL	#N/A
PICK THEM

Cell Formulas
Range	Formula
D3:D18	D3	=IFERROR(INDIRECT("'"&$F$1&"'!D" &1+A3),"")
E3:E18	E3	=IFERROR(INDEX(INPUT_SCORES!$B$2:$R$33,MATCH(D3,TEAMS,0),MATCH($F$1,WEEK_PLAYED,0)),0)
H3:H18	H3	=IFERROR(INDIRECT("'"&$F$1&"'!G"&1+A3),"")
I3:I18	I3	=IFERROR(INDEX(INPUT_SCORES!$B$2:$R$33,MATCH(H3,TEAMS,0),MATCH($F$1,WEEK_PLAYED,0)),"")
B3:B18	B3	=IFERROR(INDIRECT("'"&$F$1&"'!B"&1+A3),"")
E21, E23	E21	=VLOOKUP(D21,'AVERAGE POINTS BY TEAMS'!$B$2:$B$33,2,0)
B23	B23	=IF(B21="","",SUMPRODUCT(((DAYS)="Monday")*1,ROAD_SCORES)+SUMPRODUCT(((DAYS)="Monday")*1,HOME_TEAM_SCORES))
B25	B25	=IF(B21="","",(B21-B23))
E25	E25	=SUM(E21,E23)

Cells with Data Validation
Cell	Allow	Criteria
F1:I1	List	=WEEKS

Thanks You very much
TLS49

 

[hernantorres23]

Hi, I see changes in your sheet

Put the follow code in PICK THEM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("F1")
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then
Range("C3:C18,G3:G18").ClearContents
End If
End Sub

Put this formula in E21 =INDEX($B$3:$I$18,MATCH("Monday",DAYS,0),3)
Put this formula in E23 =INDEX($B$3:$I$18,MATCH("Monday",DAYS,0),7)

DAYS name is ='PICK THEM'!$B$3:$B$18 ...but you have this ready

 

[hernantorres23]

What are you doing with the asterisc, in columns C and G?
and
What is the source data or parameter, for fill each asterics in both columns?

 

[TLS49]

i pick either side i think team go to win game with asterics

Thanks you very much
TLS49