Forum Discussion

sf49ers19238597's avatar
sf49ers19238597
Iron Contributor
Jul 02, 2022

I have multiple worksheet need help with formulas

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

 

excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jul 02, 2022

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

     

    • sf49ers19238597's avatar
      sf49ers19238597
      Iron Contributor
      Jul 03, 2022
      I saw a problem on for friends worksheet missing week 18 data all 3 formulas

      Thanks You

Resources