Overcoming "Formula too long"

[top.jimmy]

I've created a 15 week dart league workbook and want to be able to track up
to eight teams playing each week. There are four data/score sheets in each
worksheet and one worksheet for each week. (i.e 2 teams playing against
eachother in each
scoresheet) The problem I'm having is getting the 4th score sheet to post to
my master stats worksheet due to "Formula too long." I basically index/match
each of the four scoresheets for a specific player...Here's the formula for
one player...

$C$305 =player's name, 'Mstr Stats'!$D307=the stat to look for
'WK 1'!$A$32:$W$56=the tabulated scores of the first two team's game
'WK 1'!$B$32:$B$56=the player name match
'WK 1'!$A$32:$W$32=the stat match(i.e. Ton 80's) and so on...to the
second,third or fourth game (but I can't enter the fourth table due to
"formula too long error" Imagine that???)

=IF(ISERROR(INDEX('WK 1'!$A$32:$W$56,MATCH($C$305,'WK
1'!$B$32:$B$56,0),MATCH('Mstr Stats'!$D307,'WK
1'!$A$32:$W$32,0))),0,(INDEX('WK 1'!$A$32:$W$56,MATCH($C$305,'WK
1'!$B$32:$B$56,0),MATCH('Mstr Stats'!$D307,'WK
1'!$A$32:$W$32,0))))+IF(ISERROR(INDEX('WK 1'!$Z$32:$AU$56,MATCH($C$305,'WK
1'!$Z$32:$Z$56,0),MATCH('Mstr Stats'!$D307,'WK
1'!$Z$32:$AU$32,0))),0,(INDEX('WK 1'!$Z$32:$AU$56,MATCH($C$305,'WK
1'!$Z$32:$Z$56,0),MATCH('Mstr Stats'!$D307,'WK
1'!$Z$32:$AU$32,0))))+IF(ISERROR(INDEX('WK 1'!$B$89:$W$111,MATCH($C$305,'WK
1'!$B$89:$B$111,0),MATCH('Mstr Stats'!$D307,'WK
1'!$B$89:$W$89,0))),0,(INDEX('WK 1'!$B$89:$W$111,MATCH($C$305,'WK
1'!$B$89:$B$111,0),MATCH('Mstr Stats'!$D307,'WK 1'!$B$89:$W$89,0))))

Any suggestions??? Could I 'Name' each table formula to consolidate the
formula and give more space for the fourth team game. I've gotten this far,
and I know there's a solution.

 

[bpeltzer]

Can we assume that the layout of all the different stats is the same for each
game? That is, the first column always has the name, the second column
always has stat x, the third stat y, etc? If so, then the error checking is
really only for the player's name, and I'd make two changes:
1. Change the error checking in each grid to look only for the name so that
you don't need to repeat the entire index function. Instead of
=iserror(index(match(...),match(...)),0,index(match(...),match(...)), try
=if(isna(match(...)),0,index(match(...),match(...)). Not huge, but saves ~
1/4 of the verbiage.
2. Burn a cell on the master stats sheet to associate the statistic in D307
to a column. Ex, in D308: =MATCH('Mstr Stats'!$D307,'WK 1'!$A$32:$W$32,0).
Then you only need
=if(isna(MATCH($C$305,'WK 1'!$B$32:$B$56,0)),0,index(MATCH($C$305,'WK
1'!$B$32:$B$56,0),$E308)); repeat for each score sheet w/in the Wk1
worksheet.
HTH. --Bruce

 

[top.jimmy]

Yes. Every scorsheet reference on every worksheet is exactly the same.
There are 21 stats to track per player per game & 64 players to keep up with;
using the next or adjacent cell is not prudent for the workbook I've made due
to lack of planning by me. Sooo, I'll try the ISNA function for space. If I
recall correctly, I tried that but was getting a return that I didn't want,
but I can Conditionally Format that out.

Thanks for the reply. I'll try it tonight and let you know how it turned
worked.

Thanks,
JIM