I have multiple worksheet need help with formulas

Contributor

I have 7 teams for friends worksheets. I need formula for put opponent in B5:B22, AWAY OR HOME range C5:c22

 & Time in range F5:F22. I want get data from Yearly Schedule worksheet. Look at 49ers worksheet & bye week worksheet.

 

I need help with putting what what week is each teams bye week and put number in team main worksheets & also in for friend worksheet. I want it automatically when I put in schedule Yearly Schedule worksheet.

 

tHANKS yOU

BYE WEEKS.jpgTEAMS  MAIN WORKSHEET..jpgONE FOR FRIENDS TEAMS.jpg 

2 Replies

@sf49ers164480 

 

Using the 'Chargers for friends' sheet to build the formulas. You may add these formulas to the other sheets.

 

Opponents:

 

=LET(d,'Yearly Schedule'!D2:D273,g,'Yearly Schedule'!G2:G273,FILTER(SUBSTITUTE(d&g,$A$2,""),(d=$A$2)+(g=$A$2)))

 

Team Away or Road:

 

=LET(d,'Yearly Schedule'!D2:D273,g,'Yearly Schedule'!G2:G273,f,FILTER(d&g,(d=$A$2)+(g=$A$2)),IF(SEARCH($A$2,f)=1,"ROAD","HOME"))

 

 Time:

 

=FILTER('Yearly Schedule'!I2:I273,('Yearly Schedule'!D2:D273=$A$2)+('Yearly Schedule'!G2:G273=$A$2))

 

I saw a problem on for friends worksheet missing week 18 data all 3 formulas

Thanks You