I have been working on a spreadsheet for tracking the MLB season scores. I manually enter in the date of the game (A), Away Team (B), Home Team in (C), Final Away Score (D), and Home Score in (E).
For simplicity's sake I've only used one team from here, San Francisco Giants SFG.
I have managed to use the Match function to have the sheet pull the row for each time a team plays whether they are away (H) or home (I).
From there, I have the Index function pull the date of the game (K), the teams score when they are away (L), their opponents score (M), and then calculate if they won or lost (N).
Separate table created to calculate the same thing when the team is home (P-S)
My question now, is there a way to combine these different calculations / tables into one list that will continue the calculations as new games are added?
San Francisco has played 5 games so far, 4 away and 1 at home.
I would like to combine these two lists (K-N & P-S) so it continues to pull the row, date, and scores but so I can have it in one table (U-X) so I can view everything by date.
If there's an easier way to do this let me know!
For simplicity's sake I've only used one team from here, San Francisco Giants SFG.
I have managed to use the Match function to have the sheet pull the row for each time a team plays whether they are away (H) or home (I).
From there, I have the Index function pull the date of the game (K), the teams score when they are away (L), their opponents score (M), and then calculate if they won or lost (N).
Separate table created to calculate the same thing when the team is home (P-S)
My question now, is there a way to combine these different calculations / tables into one list that will continue the calculations as new games are added?
San Francisco has played 5 games so far, 4 away and 1 at home.
I would like to combine these two lists (K-N & P-S) so it continues to pull the row, date, and scores but so I can have it in one table (U-X) so I can view everything by date.
If there's an easier way to do this let me know!
Baseball 2020.xlsx | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
1 | Date | Away Team | Home Team | Away Score | Home Score | Giants Game Rows | Giants Away | Giants Home | Combined Home & Away | |||||||||||||||||
2 | 23-Jul | SFG | LAD | 1 | 8 | SFG | Date | SFG | Op | W/L | Date | SFG | Op | W/L | Date | SFG | Op | W/L | ||||||||
3 | 23-Jul | NYY | WSN | 4 | 1 | 1 | 2 | 67 | 7/23/2020 | 1 | 8 | L | 7/28/2020 | 3 | 5 | L | ||||||||||
4 | 24-Jul | BAL | BOS | 2 | 13 | 2 | 10 | #N/A | 7/24/2020 | 1 | 9 | L | #N/A | #N/A | #N/A | #N/A | ||||||||||
5 | 24-Jul | MIN | CHW | 10 | 5 | 3 | 24 | #N/A | 7/25/2020 | 5 | 4 | W | #N/A | #N/A | #N/A | #N/A | ||||||||||
6 | 24-Jul | MIL | CHC | 0 | 3 | 4 | 39 | #N/A | 7/26/2020 | 3 | 1 | W | #N/A | #N/A | #N/A | #N/A | ||||||||||
7 | 24-Jul | DET | CIN | 1 | 7 | 5 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | ||||||||||
8 | 24-Jul | KCR | CLE | 0 | 2 | 6 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | ||||||||||
9 | 24-Jul | SEA | HOU | 2 | 8 | 7 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | ||||||||||
10 | 24-Jul | SFG | LAD | 1 | 9 | 8 | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | #N/A | ||||||||||
Scores |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H3 | H3 | =MATCH($H$2,OFFSET($B$1,F2,0,200,1),0)+F2 |
I3 | I3 | =MATCH($H$2,OFFSET($C$1,G2,0,200,1),0)+G2 |
H4:H10 | H4 | =MATCH($H$2,OFFSET($B$1,H3,0,200,1),0)+H3 |
I4:I10 | I4 | =MATCH($H$2,OFFSET($C$1,I3,0,200,1),0)+I3 |
K3:K10 | K3 | =INDEX(MLB[Date],H3-1) |
L3:L10 | L3 | =INDEX(MLB[Away Score],H3-1) |
M3:M10 | M3 | =INDEX(MLB[Home Score],H3-1) |
S3:S10,N3:N10 | N3 | =IF(L3>M3,"W","L") |
P3:P10 | P3 | =INDEX(MLB[Date],I3-1) |
Q3:Q10 | Q3 | =INDEX(MLB[Home Score],I3-1) |
R3:R10 | R3 | =INDEX(MLB[Away Score],I3-1) |