I have MAP functions in rows 10 and 11 that references defined tables and named ranges on a sheet. The functions work fine.
When I try to insert a row below row 9 or 12 to make more room, all the MAP functions in rows 10/11 get a #REF error. The table range has moved but everything still seems to be the same in the formula? I hate to reconstruct the entire sheet, but I don't know what's going on.
When I try to insert a row below row 9 or 12 to make more room, all the MAP functions in rows 10/11 get a #REF error. The table range has moved but everything still seems to be the same in the formula? I hate to reconstruct the entire sheet, but I don't know what's going on.
| 2022 MCC NCAA.xlsx | |||||||
|---|---|---|---|---|---|---|---|
| B | C | D | E | F | |||
| 2 | Scoring | Round | Points | ||||
| 3 | 1 | 10 | |||||
| 4 | 2 | 20 | |||||
| 5 | 3 | 40 | |||||
| 6 | 4 | 100 | |||||
| 7 | 5 | 150 | |||||
| 8 | 6 | 300 | |||||
| 9 | |||||||
| 10 | Player Points | 0 | 0 | ||||
| 11 | Max Remaining Pts | 0 | 0 | ||||
| 12 | Rank | 1 | 2 | ||||
| 13 | Teams | Rounds Won | Still Playing | Player 1 | Player 2 | ||
| 14 | Akron (13) | 0 | TRUE | ||||
| 15 | Alabama (6) | 0 | TRUE | ||||
| 16 | Arizona (1) | 0 | TRUE | ||||
| 17 | Arkansas (4) | 0 | TRUE | ||||
| 18 | Auburn (2) | 0 | TRUE | ||||
| 19 | Baylor (1) | 0 | TRUE | ||||
| 20 | Boise State (8) | 0 | TRUE | ||||
| 21 | Chattanooga (13) | 0 | TRUE | ||||
| 22 | Colgate (14) | 0 | TRUE | ||||
PPR Player Picks | |||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E10 | E10 | =SUM( MAP( tblPPRPicks[Rounds Won], tblPPRPicks[Player 1]+0, LAMBDA(a,b,SUMPRODUCT( (RndPoints) * ((RndName) <= MIN(a,b)) ) ) ) ) |
| F10 | F10 | =SUM( MAP( tblPPRPicks[Rounds Won], tblPPRPicks[Player 2]+0, LAMBDA(a,b,SUMPRODUCT( (RndPoints) * ((RndName) <= MIN(a,b)) ) ) ) ) |
| E11 | E11 | =SUM( MAP(tblPPRPicks[Rounds Won], tblPPRPicks[Still Playing], tblPPRPicks[Player 1], LAMBDA(a,b,c, SUMPRODUCT( RndPoints, (RndName > a) * ((RndName <= c) * b) ) ) ) ) |
| F11 | F11 | =SUM( MAP(tblPPRPicks[Rounds Won], tblPPRPicks[Still Playing], tblPPRPicks[Player 2], LAMBDA(a,b,c, SUMPRODUCT( RndPoints, (RndName > a) * ((RndName <= c) * b) ) ) ) ) |
| E12:F12 | E12 | =RANK.EQ(E10,$E$10:$X$10) + COUNTIF($E$10:E$10,E$10) - 1 |
| B14:B22 | B14 | =INDEX(SortedTeamList,ROW(1:1)) |
| C14:C22 | C14 | =XLOOKUP($B14,TeamList,WinList) |
| D14:D22 | D14 | =XLOOKUP([@Teams],TeamList,LostList) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| LostList | ='Results Bracket'!$AH$88:$AH$151 | D14:D22 |
| RndName | ='PPR Player Picks'!$E$3:$E$8 | B16, E10:F11 |
| RndPoints | ='PPR Player Picks'!$F$3:$F$8 | B16, E10:F11 |
| SortedTeamList | ='Results Bracket'!$X$88:$X$151 | B14:B22 |
| TeamList | ='Results Bracket'!$V$88:$V$151 | C14:D22 |
| WinList | ='Results Bracket'!$Z$88:$Z$151 | C14:C22 |
