I have multiple worksheet need help with formulas

[sf49ers19238597]

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

 

[#2]

@sf49ers19238597 

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))

[#3]

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

Thanks You