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

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
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,"")
AD4:AD11AD4=IF(AND(Series="East",Game=3,Team=$Y4),SPRD,"")
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,"")
AN14:AN21,AN4:AN11AN4=IFERROR(AVERAGE(Z4,AB4,AD4,AF4,AH4,AJ4,AL4),"")
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,"")
AD14:AD21AD14=IF(AND(Series="West",Game=3,Team=$Y14),SPRD,"")
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
SPRD='Playoff Favs'!$E$4:$E$19AB14:AB21, AD14:AD21, AF14:AF21, AH14:AH21, AJ14:AJ21, AL14:AL21, Z4:Z11, AB4:AB11, AD4:AD11, AF4:AF11, AH4:AH11, AJ4:AJ11, AL4:AL11, Z14:Z21, P4:Q4
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
 

Some videos you may like

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
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Or maybe use
=IF(countifs(Series,"West",Game,1,Team,$Y14)>0,SPRD,"")
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
Oops, missed that SPRD was another named range, rather than a string.
 

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
174
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Or maybe use
=IF(countifs(Series,"West",Game,1,Team,$Y14)>0,SPRD,"")
Thank you for the reply.
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.
Thank you for the reply.
Works very well.
thx
NBA.xlsm
ABACADAEAFAG
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),"")
AD14:AD21AD14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),SPRD),"")
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
SPRD='Playoff Favs'!$E$4:$E$19AB14:AB21, AD14:AD21, AF14:AF21
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
ABACADAEAFAG
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),"")
AD14:AD21AD14=IFERROR(LOOKUP(2,1/(Series="West")/(Game=3)/(Team=$Y14),SPRD),"")
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
SPRD='Playoff Favs'!$E$4:$E$19AB14:AB21, AD14:AD21, AF14:AF21
Team='Playoff Favs'!$F$4:$F$19AB14:AG21
WL='Playoff Favs'!$D$4:$D$19AC14:AC21, AE14:AE21, AG14:AG21
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
My formula won't work, as I misread your formula & thought that SPRD was a string, rather than a named range.
 

gtd526

Board Regular
Joined
Jul 30, 2013
Messages
174
Office Version
  1. 2019
Platform
  1. Windows
No problem.
What would your new formula be?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,076
Office Version
  1. 365
Platform
  1. Windows
You already have a working formulae :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top