I need to pull data from multiple tabs into one main tab using index match.
I am football statistician for local high school. Trying to create a "career stats" sheet for all players. I have separate stat sheets for each season "2016","2017","2018","2019", ect. I need to pull each players stats from each yearly sheet for every statistical category. In the example sheets below, in my "career stats" tab, I enter the player name in cell B2 and then the year of graduation in cell D2. Once the year of graduation is entered, the seasons (year) are automatically calculated from FR to SR in cells (D4:D7).
For "PLAYER 1" (B2), his freshman (FR) year was 2017 (D4). When the result in cell "D4" returns 2017 (=D2-3), I need the "Career Stats" sheet to find "Player 1" from sheet "2017" and then return the stats for each statistical category (E3:S3)and return results in (E4:S4). Likewise, "D5" returns 2018, therefore, the career sheet needs to pull from sheet "2018", ect. I don't know if it's possible to use the result in column D in order to find the corresponding sheet and the Player. My hope is that when the result in column D of the "Career Stats" sheet changes the year, that it knows automatically to use that sheet to find the result. I would like to only enter the "player name" in "B2" and year of graduation in "D2" and everything else calculates automatically.
In the attached example, I have pasted the results that are needed to be returned. If possible to post the formula for cell "K4" that will return "25" and "P5" that will return "217", it would be greatly appreciated.
"CAREER STATS"
"2017"
"2018"
OUTPUT NEEDED IN "CAREER STATS" FOR PLAYER 1
I am football statistician for local high school. Trying to create a "career stats" sheet for all players. I have separate stat sheets for each season "2016","2017","2018","2019", ect. I need to pull each players stats from each yearly sheet for every statistical category. In the example sheets below, in my "career stats" tab, I enter the player name in cell B2 and then the year of graduation in cell D2. Once the year of graduation is entered, the seasons (year) are automatically calculated from FR to SR in cells (D4:D7).
For "PLAYER 1" (B2), his freshman (FR) year was 2017 (D4). When the result in cell "D4" returns 2017 (=D2-3), I need the "Career Stats" sheet to find "Player 1" from sheet "2017" and then return the stats for each statistical category (E3:S3)and return results in (E4:S4). Likewise, "D5" returns 2018, therefore, the career sheet needs to pull from sheet "2018", ect. I don't know if it's possible to use the result in column D in order to find the corresponding sheet and the Player. My hope is that when the result in column D of the "Career Stats" sheet changes the year, that it knows automatically to use that sheet to find the result. I would like to only enter the "player name" in "B2" and year of graduation in "D2" and everything else calculates automatically.
In the attached example, I have pasted the results that are needed to be returned. If possible to post the formula for cell "K4" that will return "25" and "P5" that will return "217", it would be greatly appreciated.
"CAREER STATS"
1 | PLAYER 1 | CLASS | 2020 | Passing | Rushing | Receiving | Scoring | ||||||||||||||||
RB/DB | 6-0 | 185 | Comp | Att | Yards | TD | Int | Att | Yards | Avg. | TD | 2pt | Rec | Yards | Avg. | TD | 2pt. | TD | 2pt | XP | FG | Total | |
PLAYER 1 | FR | 2017 | |||||||||||||||||||||
PLAYER 1 | SO | 2018 | |||||||||||||||||||||
PLAYER 1 | JR | 2019 | |||||||||||||||||||||
PLAYER 1 | SR | 2020 | |||||||||||||||||||||
"2017"
2017 | Passing | Rushing | Receiving | Scoring | |||||||||||||||||||
SEASON STATS | Comp | Att | Yards | TD | Int | Att | Yards | Avg. | TD | 2pt | Rec | Yards | Avg. | TD | 2pt. | TD | 2pt | XP | FG | Total | |||
1 | PLAYER 1 | FR | 2017 | 0 | 0 | 0 | 0 | 0 | 6 | 25 | 4.2 | 0 | 0 | 1 | 9 | 9.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2 | PLAYER 2 | SO | 2017 | 30 | 67 | 402 | 1 | 6 | 2 | 16 | 8.0 | 0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
4 | PLAYER 4 | SO | 2017 | 0 | 0 | 0 | 0 | 0 | 8 | 36 | 4.5 | 0 | 0 | 12 | 158 | 13.2 | 1 | 0 | 1 | 0 | 0 | 0 | 6 |
6 | PLAYER 6 | FR | 2017 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0.0 | 0 | 0 | 17 | 235 | 13.8 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
"2018"
2018 | Passing | Rushing | Receiving | Scoring | |||||||||||||||||||
SEASON STATS | Comp | Att | Yards | TD | Int | Att | Yards | Avg. | TD | 2pt | Rec | Yards | Avg. | TD | 2pt. | TD | 2pt | XP | FG | Total | |||
1 | PLAYER 1 | SO | 2018 | 0 | 0 | 0 | 0 | 0 | 22 | 93 | 4.2 | 1 | 0 | 15 | 217 | 14.5 | 3 | 0 | 4 | 0 | 0 | 0 | 24 |
2 | PLAYER 2 | JR | 2018 | 122 | 278 | 1353 | 8 | 14 | 43 | 124 | 2.9 | 6 | 0 | 0 | 0 | 0.0 | 0 | 0 | 6 | 0 | 0 | 0 | 36 |
4 | PLAYER 4 | JR | 2018 | 0 | 0 | 0 | 0 | 0 | 14 | 57 | 4.1 | 1 | 0 | 47 | 468 | 10.0 | 3 | 0 | 4 | 0 | 0 | 0 | 24 |
5 | PLAYER 5 | FR | 2018 | 0 | 0 | 0 | 0 | 0 | 4 | 59 | 14.8 | 0 | 0 | 17 | 216 | 12.7 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
6 | PLAYER 6 | SO | 2018 | 0 | 0 | 0 | 0 | 0 | 1 | 2 | 2.0 | 0 | 0 | 43 | 452 | 10.5 | 2 | 0 | 2 | 0 | 0 | 0 | 12 |
OUTPUT NEEDED IN "CAREER STATS" FOR PLAYER 1
1 | PLAYER 1 | CLASS | 2020 | Passing | Rushing | Receiving | Scoring | ||||||||||||||||
RB/DB | 6-0 | 185 | Comp | Att | Yards | TD | Int | Att | Yards | Avg. | TD | 2pt | Rec | Yards | Avg. | TD | 2pt. | TD | 2pt | XP | FG | Total | |
PLAYER 1 | FR | 2017 | 0 | 0 | 0 | 0 | 0 | 6 | 25 | 4.2 | 0 | 0 | 1 | 9 | 9.0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
PLAYER 1 | SO | 2018 | 0 | 0 | 0 | 0 | 0 | 22 | 93 | 4.2 | 1 | 0 | 15 | 217 | 14.5 | 3 | 0 | 4 | 0 | 0 | 0 | 24 | |
PLAYER 1 | JR | 2019 | 0 | 0 | 0 | 0 | 0 | 85 | 673 | 7.9 | 8 | 0 | 35 | 498 | 14.2 | 3 | 0 | 11 | 0 | 0 | 0 | 66 | |
PLAYER 1 | SR | 2020 |