# Formula works in one section but not in another???

#### gtd526

##### Board Regular
Hello,
Im using the following formula:
=IF(AND(Series="East",Game=1,Team=\$Y4),SPRD,"")
working fine in one section.
the second formula is:
=IF(AND(Series="West",Game=1,Team=\$Y14),SPRD,"")
is not working in another section?

Sorry for the amount of data.

NBA.xlsm
YZAA
2Eastern
3TeamSPRDGM1
4MIL-1.5W
5ORL-2L
6IND-3.5W
7MIA-4L
8BOS-5W
9PHI-6L
10TOR
11BRK
12Western
13TeamSPRDGM1
14LAL
15POR
16HOU
17OKC
18DEN
19UTA
20LAC#VALUE!#VALUE!
21DAL#VALUE!#VALUE!
Playoff Favs
Cell Formulas
RangeFormula
Z4:Z11Z4=IF(AND(Series="East",Game=1,Team=\$Y4),SPRD,"")
AA4:AA11AA4=IF(AND(Series="East",Game=1,Team=\$Y4),WL,"")
Z14:Z21Z14=IF(AND(Series="West",Game=1,Team=\$Y14),SPRD,"")
AA14:AA21AA14=IF(AND(Series="West",Game=1,Team=\$Y14),WL,"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19Z4:AA4
Series='Playoff Favs'!\$B\$4:\$B\$19Z4:AA4
SPRD='Playoff Favs'!\$E\$4:\$E\$19Z4:Z11, Z14:Z21
Team='Playoff Favs'!\$F\$4:\$F\$19Z4:AA4
WL='Playoff Favs'!\$D\$4:\$D\$19AA4:AA11, AA14:AA21

Where Im pulling the data from:
NBA.xlsm
ABCDEF
1Copy Teams From "Fav's"
2reversereversereverseATSreverse
3SectionSeriesGame*W/L*SPRDTEAM
4SemiEast1W-1.5MIL
5SemiEast1L-2ORL
6SemiEast1W-3.5IND
7SemiEast1L-4MIA
8SemiEast1W-5BOS
9SemiEast1L-6PHI
10SemiEast2W-3.5TOR
11SemiEast2L-2BRK
12SemiWest1W-1.5LAL
13SemiWest1L-2POR
14SemiWest2W-3.5HOU
15SemiWest2L-4OKC
16SemiWest1W-5DEN
17SemiWest1L-6UTA
18SemiWest2W-3.5LAC
19SemiWest2L-2DAL
Playoff Favs
Cell Formulas
RangeFormula
A4:A19A4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$A\$5:\$A\$28,Favs!\$H\$5:\$H\$28),2,0),"")
B4:B19B4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$B\$5:\$B\$28,Favs!\$H\$5:\$H\$28),2,0),"")
C4:C19C4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$C\$5:\$C\$28,Favs!\$H\$5:\$H\$28),2,0),"")
E4:E19E4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$D\$5:\$D\$28,,Favs!\$H\$5:\$H\$28),2,0),"")
Named Ranges
NameRefers ToCells
Team='Playoff Favs'!\$F\$4:\$F\$19E4, A4:C4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO

Below is ALL.
NBA.xlsm
1Copy Teams From "Fav's"+/-
2reversereversereverseATSreverseFav'sIs +/-ATSEastern
3SectionSeriesGame*W/L*SPRDTEAMW%PTSFG%3P%REB+/-> ThanMy FavW %> ATS ?DiffFav'svs SPRDPreviousTotalW%TeamSPRDGM1SPRDGM2SPRDGM3SPRDGM4SPRDGM5SPRDGM6SPRDGM7Avg SPRDWLW%Team
4SemiEast1W-1.5MIL75%11648.939.14986MIL75%Yes6.56W:22467%MIL-1.5W            -1.51 100%MIL
5SemiEast1L-2ORL25%10842.738.042-8    -10.0L:213ORL-2L            -2 1 ORL
6SemiEast1W-3.5IND 10145.537.537-11    -14.05W:213100%IND-3.5W            -3.51 100%IND
7SemiEast1L-4MIA100%11146.439.146116MIA100%Yes6.5L:  MIA-4L            -4 1 MIA
8SemiEast1W-5BOS100%11245.235.043125BOS100%Yes6.84W:  #DIV/0!BOS-5W            -51 100%BOS
9SemiEast1L-6PHI 10139.626.446-121   -17.8L:  PHI-6L            -6 1 PHI
10SemiEast2W-3.5TOR100%12649.543.351216TOR100%Yes17.03W:11 TOR  -3.5W          -3.51 100%TOR
11SemiEast2L-2BRK 10639.733.744-21    -22.5L:112BRK  -2L          -2 1 BRK
12SemiWest1W-1.5LAL75%113.846.933.150115LAL75%Yes9.52W:112100%Western
13SemiWest1L-2POR25%102.841.435.743-111   -13.0L:11TeamSPRDGM1SPRDGM2SPRDGM3SPRDGM4SPRDGM5SPRDGM6SPRDGM7Avg SPRDWLW%Team
14SemiWest2W-3.5HOU50%113.844.135.64133HOU50% -0.2LAL                  LAL
15SemiWest2L-4OKC50%110.545.535.049-32   -7.3Copy/Paste SVPOR                  POR
16SemiWest1W-5DEN25%113.546.242.742-12    -17.0To: Oddsafter ea gameHOU                  HOU
17SemiWest1L-6UTA75%125.551.643.344126UTA75%Yes6.0to preserve recordOKC                  OKC
18SemiWest2W-3.5LAC50%123.847.535.44402   -3.2To: FavsDEN                  DEN
19SemiWest2L-2DAL50%123.549.238.44703DAL50% -2.3IFS(test1, value-if-true, test2, value-if-true)UTA                  UTA
20#VALUE!LAC#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!    LAC
21IF(OR(A5="Red",B5="Green"),TRUE,FALSE)DAL#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!    DAL
Playoff Favs
Cell Formulas
RangeFormula
A4:A19A4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$A\$5:\$A\$28,Favs!\$H\$5:\$H\$28),2,0),"")
B4:B19B4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$B\$5:\$B\$28,Favs!\$H\$5:\$H\$28),2,0),"")
C4:C19C4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$C\$5:\$C\$28,Favs!\$H\$5:\$H\$28),2,0),"")
G4:G19G4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$G\$20,7,0),"")
H4:H19H4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$I\$20,9,0),"")
I4:I19I4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$L\$20,12,0),"")
J4:J19J4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$O\$20,15,0),"")
K4:K19K4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$U\$20,21,0),"")
L4:L19L4=IFERROR(VLOOKUP(\$F4,Playoffs!\$A\$2:\$AC\$20,29,0),"")
M4,M18,M16,M14,M12,M10,M8,M6M4=COUNTIF(G4,">"&G5)+COUNTIF(H4,">"&H5)+COUNTIF(I4,">"&I5)+COUNTIF(J4,">"&J5)+COUNTIF(K4,">"&K5)+COUNTIF(L4,">"&L5)
N4,N18,N16,N14,N12,N10,N8,N6N4=IF(M4>M5,F4,IF(M4=M5,"Tie",""))
O4:O19O4=IFERROR(VLOOKUP(\$N4,Playoffs!\$A\$2:\$G\$21,7,0),"")
P4:P19P4=IFERROR(IF(\$E4<>0,IF(((E4*-1)<L4),"Yes",""),""),"")
Q4:Q19Q4=IFERROR(L4-(E4*-1),"")
M5,M19,M17,M15,M13,M11,M9,M7M5=COUNTIF(G5,">"&G4)+COUNTIF(H5,">"&H4)+COUNTIF(I5,">"&I4)+COUNTIF(J5,">"&J4)+COUNTIF(K5,">"&K4)+COUNTIF(L5,">"&L4)
N5,N19,N17,N15,N13,N11,N9,N7N5=IF(M5>M4,F5,IF(M4=M5,"Tie",""))
W4,W12,W10,W8,W6W4=U4/(U4+U5)
Z4:Z11Z4=IF(AND(Series="East",Game=1,Team=\$Y4),SPRD,"")
AA4:AA11AA4=IF(AND(Series="East",Game=1,Team=\$Y4),WL,"")
AB4:AB11AB4=IF(AND(Series="East",Game=2,Team=\$Y4),SPRD,"")
AC4:AC11AC4=IF(AND(Series="East",Game=2,Team=\$Y4),WL,"")
AE4:AE11AE4=IF(AND(Series="East",Game=3,Team=\$Y4),WL,"")
AF4:AF11AF4=IF(AND(Series="East",Game=4,Team=\$Y4),SPRD,"")
AG4:AG11AG4=IF(AND(Series="East",Game=4,Team=\$Y4),WL,"")
AH4:AH11AH4=IF(AND(Series="East",Game=5,Team=\$Y4),SPRD,"")
AI4:AI11AI4=IF(AND(Series="East",Game=5,Team=\$Y4),WL,"")
AJ4:AJ11AJ4=IF(AND(Series="East",Game=6,Team=\$Y4),SPRD,"")
AK4:AK11AK4=IF(AND(Series="East",Game=6,Team=\$Y4),WL,"")
AL4:AL11AL4=IF(AND(Series="East",Game=7,Team=\$Y4),SPRD,"")
AM4:AM11AM4=IF(AND(Series="East",Game=7,Team=\$Y4),WL,"")
AO14:AO21,AO4:AO11AO4=COUNTIF(Z4:AM4,"W")
AP14:AP21,AP4:AP11AP4=COUNTIF(Z4:AM4,"L")
AQ14:AQ21,AQ4:AQ11AQ4=IFERROR(AO4/(AO4+AP4),"")
AR14:AR21,AR4:AR11AR4=Y4
T4T4=COUNTIFS(\$M\$4:\$M\$26,"6",\$D\$4:\$D\$26,"W")
T5T5=COUNTIFS(\$M\$4:\$M\$26,"6",\$D\$4:\$D\$26,"L")
T6T6=COUNTIFS(\$M\$4:\$M\$26,"5",\$D\$4:\$D\$26,"W")
T7T7=COUNTIFS(\$M\$4:\$M\$26,"5",\$D\$4:\$D\$26,"L")
T8T8=COUNTIFS(\$M\$4:\$M\$26,"4",\$D\$4:\$D\$26,"W")
T9T9=COUNTIFS(\$M\$4:\$M\$26,"4",\$D\$4:\$D\$26,"L")
T10T10=COUNTIFS(\$M\$4:\$M\$26,"3",\$D\$4:\$D\$26,"W")
T11T11=COUNTIFS(\$M\$4:\$M\$26,"3",\$D\$4:\$D\$26,"L")
T12T12=COUNTIFS(\$M\$4:\$M\$26,"2",\$D\$4:\$D\$26,"W")
T13T13=COUNTIFS(\$M\$4:\$M\$26,"2",\$D\$4:\$D\$26,"L")
V4:V13V4=T4+U4
Z14:Z21Z14=IF(AND(Series="West",Game=1,Team=\$Y14),SPRD,"")
AA14:AA21AA14=IF(AND(Series="West",Game=1,Team=\$Y14),WL,"")
AB14:AB21AB14=IF(AND(Series="West",Game=2,Team=\$Y14),SPRD,"")
AC14:AC21AC14=IF(AND(Series="West",Game=2,Team=\$Y14),WL,"")
AE14:AE21AE14=IF(AND(Series="West",Game=3,Team=\$Y14),WL,"")
AF14:AF21AF14=IF(AND(Series="West",Game=4,Team=\$Y14),SPRD,"")
AG14:AG21AG14=IF(AND(Series="West",Game=4,Team=\$Y14),WL,"")
AH14:AH21AH14=IF(AND(Series="West",Game=5,Team=\$Y14),SPRD,"")
AI14:AI21AI14=IF(AND(Series="West",Game=5,Team=\$Y14),WL,"")
AJ14:AJ21AJ14=IF(AND(Series="West",Game=6,Team=\$Y14),SPRD,"")
AK14:AK21AK14=IF(AND(Series="West",Game=6,Team=\$Y14),WL,"")
AL14:AL21AL14=IF(AND(Series="West",Game=7,Team=\$Y14),SPRD,"")
AM14:AM21AM14=IF(AND(Series="West",Game=7,Team=\$Y14),WL,"")
E4:E19E4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$D\$5:\$D\$28,,Favs!\$H\$5:\$H\$28),2,0),"")
S20S20=XOR(Game=2,Series="West",Team=\$Y14)
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19Z4:AM4
Series='Playoff Favs'!\$B\$4:\$B\$19Z4:AM4
Team='Playoff Favs'!\$F\$4:\$F\$19Z4:AM4, N4, G4:L4, E4, A4:C4
WL='Playoff Favs'!\$D\$4:\$D\$19T4:T13, AC14:AC21, AE14:AE21, AG14:AG21, AI14:AI21, AK14:AK21, AM14:AM21, AA4:AA11, AC4:AC11, AE4:AE11, AG4:AG11, AI4:AI11, AK4:AK11, AM4:AM11, AA14:AA21
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Q4:Q21Expression="IF(\$M4=""Yes"")"textNO
P4:P21Cell Value="Yes"textNO
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO
L4:L19Expression=L4=MAX(OFFSET(L\$4,FLOOR(ROW(L4)-ROW(L\$4),2),0,2,1))textNO
G4:K19Expression=G4=MAX(OFFSET(G\$4,FLOOR(ROW(G4)-ROW(G\$4),2),0,2,1))textNO
M4:M19Cell Valuetop 1 bottom valuestextNO
M4:M19Expression=M4=MAX(OFFSET(M\$4,FLOOR(ROW(M4)-ROW(M\$4),2),0,2,1))textNO

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### jasonb75

##### Well-known Member
This part is where the problem lies, your named ranges and in row 19, the errors start in row 20.

It goes without saying that you need a different type of formula for this to work correctly, a smaller example with a description of what the formula should be doing would help.

NBA.xlsm
YZAA
2Eastern
3TeamSPRDGM1
4MIL-1.5W
5ORL-2L
6IND-3.5W
7MIA-4L
8BOS-5W
9PHI-6L
10TOR
11BRK
12Western
13TeamSPRDGM1
14LAL
15POR
16HOU
17OKC
18DEN
19UTA
20LAC#VALUE!#VALUE!
21DAL#VALUE!#VALUE!
Playoff Favs
Cell Formulas
RangeFormula
Z4:Z11Z4=IF(AND(Series="East",Game=1,Team=\$Y4),SPRD,"")
AA4:AA11AA4=IF(AND(Series="East",Game=1,Team=\$Y4),WL,"")
Z14:Z21Z14=IF(AND(Series="West",Game=1,Team=\$Y14),SPRD,"")
AA14:AA21AA14=IF(AND(Series="West",Game=1,Team=\$Y14),WL,"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19Z4:AA4
Series='Playoff Favs'!\$B\$4:\$B\$19Z4:AA4
SPRD='Playoff Favs'!\$E\$4:\$E\$19Z4:Z11, Z14:Z21
Team='Playoff Favs'!\$F\$4:\$F\$19Z4:AA4
WL='Playoff Favs'!\$D\$4:\$D\$19AA4:AA11, AA14:AA21

#### jasonb75

##### Well-known Member
Following up, best guess on the formula for Z4 without more information.

=IFERROR(LOOKUP(2,1/(Series="East")/(Game=1)/(Team=\$Y4),SPRD),"")

If that is what you need then the same method could be applied to the other formulas.

#### Fluff

##### MrExcel MVP, Moderator
Or maybe use
=IF(countifs(Series,"West",Game,1,Team,\$Y14)>0,SPRD,"")

#### jasonb75

##### Well-known Member

I think that is going to encounter the same error as the original formula, Fluff.

#### Fluff

##### MrExcel MVP, Moderator
Oops, missed that SPRD was another named range, rather than a string.

#### gtd526

##### Board Regular

Or maybe use
=IF(countifs(Series,"West",Game,1,Team,\$Y14)>0,SPRD,"")
GM1 is correct.
SPRD is incorrect. It seems the be shifting down by 2 rows for the response.
NBA.xlsm
YZAA
12Western
13TeamSPRDGM1
14LAL-3.5W
15POR-4L
16HOU
17OKC
18DEN-3.5W
19UTA-2L
20LAC
21DAL
Playoff Favs
Cell Formulas
RangeFormula
Z14:Z21Z14=IF(COUNTIFS(Series,"West",Game,1,Team,\$Y14)>0,SPRD,"")
AA14:AA21AA14=IF(COUNTIFS(Series,"West",Game,1,Team,\$Y14)>0,WL,"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19Z14:AA21
Series='Playoff Favs'!\$B\$4:\$B\$19Z14:AA21
SPRD='Playoff Favs'!\$E\$4:\$E\$19Z14:Z21
Team='Playoff Favs'!\$F\$4:\$F\$19Z14:AA21
WL='Playoff Favs'!\$D\$4:\$D\$19AA14:AA21

NBA.xlsm
EF
3SPRDTEAM
4-1.5MIL
5-2ORL
6-3.5IND
7-4MIA
8-5BOS
9-6PHI
10-3.5TOR
11-2BRK
12-1.5LAL
13-2POR
14-3.5HOU
15-4OKC
16-5DEN
17-6UTA
18-3.5LAC
19-2DAL
Playoff Favs
Cell Formulas
RangeFormula
E4:E19E4=IFERROR(VLOOKUP(\$F4,CHOOSE({1,2,3},Favs!\$G\$5:\$G\$28,Favs!\$D\$5:\$D\$28,,Favs!\$H\$5:\$H\$28),2,0),"")
Named Ranges
NameRefers ToCells
Team='Playoff Favs'!\$F\$4:\$F\$19E4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D4:E21Cell Value="L"textNO
D4:E21Cell Value="W"textNO

Following up, best guess on the formula for Z4 without more information.

=IFERROR(LOOKUP(2,1/(Series="East")/(Game=1)/(Team=\$Y4),SPRD),"")

If that is what you need then the same method could be applied to the other formulas.
Works very well.
thx
NBA.xlsm
13SPRDGM2SPRDGM3SPRDGM4
14
15
16-3.5L
17-4L
18  -5W
19  -6W
20    -3.5L
21    -2L
Playoff Favs
Cell Formulas
RangeFormula
AB14:AB21AB14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=\$Y14),SPRD),"")
AC14:AC21AC14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=\$Y14),WL),"")
AE14:AE21AE14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=\$Y14),WL),"")
AF14:AF21AF14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=\$Y14),SPRD),"")
AG14:AG21AG14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=\$Y14),WL),"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19AB14:AG21
Series='Playoff Favs'!\$B\$4:\$B\$19AB14:AG21
Team='Playoff Favs'!\$F\$4:\$F\$19AB14:AG21
WL='Playoff Favs'!\$D\$4:\$D\$19AC14:AC21, AE14:AE21, AG14:AG21

Following up, best guess on the formula for Z4 without more information.

=IFERROR(LOOKUP(2,1/(Series="East")/(Game=1)/(Team=\$Y4),SPRD),"")

If that is what you need then the same method could be applied to the other formulas.
Thanks for the reply. Working!! thx.
NBA.xlsm
13SPRDGM2SPRDGM3SPRDGM4
14
15
16-3.5L
17-4L
18  -5W
19  -6W
20    -3.5L
21    -2L
Playoff Favs
Cell Formulas
RangeFormula
AB14:AB21AB14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=\$Y14),SPRD),"")
AC14:AC21AC14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=2)/(Team=\$Y14),WL),"")
AE14:AE21AE14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=\$Y14),WL),"")
AF14:AF21AF14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=\$Y14),SPRD),"")
AG14:AG21AG14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=4)/(Team=\$Y14),WL),"")
Named Ranges
NameRefers ToCells
Game='Playoff Favs'!\$C\$4:\$C\$19AB14:AG21
Series='Playoff Favs'!\$B\$4:\$B\$19AB14:AG21
Team='Playoff Favs'!\$F\$4:\$F\$19AB14:AG21
WL='Playoff Favs'!\$D\$4:\$D\$19AC14:AC21, AE14:AE21, AG14:AG21

#### Fluff

##### MrExcel MVP, Moderator
My formula won't work, as I misread your formula & thought that SPRD was a string, rather than a named range.

#### gtd526

##### Board Regular
No problem.
What would your new formula be?

#### Fluff

##### MrExcel MVP, Moderator
You already have a working formulae

Replies
3
Views
54
Replies
3
Views
249
Replies
5
Views
117
Replies
13
Views
193
Replies
9
Views
77