Forum Discussion

Grassland79's avatar
Grassland79
Copper Contributor
Jan 03, 2025

Calculations Across sheets

Hi there.  I have a spreadsheet where my first sheet called "Rankings" is a list of teams in column A and column B is their season-long rankings.  For instance, the first row is A2 = Arizona, B2 = 102, the second row is A3 = Atlanta, B3 = 96, the third row is A4 = Baltimore, B4 = 65, the fourth row is A5 = Buffalo, B5 = 76, etc.  In the same spreadsheet, my second sheet is called "Week1" and that has the weekly matchups.  For instance, if Arizona is playing Baltimore in week1 and Atlanta is playing Buffalo, B2 = Arizona and B3 = Baltimore while B5 = Atlanta and B6 = Buffalo.  In the week1 sheet, column C is a game rating.  What I need to do for column C in that week1 sheet is retrieve the ranking for the teams in B2 and B3 (in this case Arizona and Baltimore).  Then I need to subtract the smaller number (ranking) from the larger number and divide that result by 7.  That result would go in cell C3 on the week1 sheet.  Similarly, proceeding to the next matchup, I need to retrieve the ranking for the teams in B5 and B6 (in this case Atlanta and Buffalo).  Then I need to subtract the smaller number from the larger number and divide that result by 7.  That result would go in cell C6. I am not sure how to retrieve the values in the "Rankings" sheet into the correct cells (Column C) on the week1 sheet based on which teams are playing each other.  Any help would be appreciated.  Thanks!