How to simplfy If & Sumif Code

I have data from different worksheet according to the week number. I am using
If function to look for the data for over different worksheet. My problem is
every week there will be a new weeksheet and I have to edit the code to
include the new week. Is there a simplier solution? Thanks.

=IF(SUMIF('[Daily Output (2006).xls]Week14'!$F:$F,B4,'[Daily Output
(2006).xls]Week14'!$J:$J),SUMIF('[Daily Output
(2006).xls]Week14'!$F:$F,B4,'[Daily Output
(2006).xls]Week14'!$J:$J),IF(SUMIF('[Daily Output
(2006).xls]Week15'!$F:$F,B4,'[Daily Output
(2006).xls]Week15'!$J:$J),SUMIF('[Daily Output
(2006).xls]Week15'!$F:$F,B4,'[Daily Output
(2006).xls]Week15'!$J:$J),IF(SUMIF('[Daily Output
(2006).xls]Week16'!$F:$F,B4,'[Daily Output
(2006).xls]Week16'!$J:$J),SUMIF('[Daily Output
(2006).xls]Week16'!$F:$F,B4,'[Daily Output
(2006).xls]Week16'!$J:$J),SUMIF('[Daily Output
(2006).xls]Week17'!$E:$E,B4,'[Daily Output (2006).xls]Week17'!$I:$I))))

Create a 4x2 staging table (eg in AA1:AB4)
in AA1A:A4, enter formula:
="'[Daily Output (2006).xls]Week"&AB1&"'"
in AB1, enter 1st week no. (14)
in AB2 thru AB4, enter: = B1+1

Modify your formula as:
=IF(SUMIF(INDIRECT(AA1&"!$F:$F),B4,INDIRECT(AA1&"!$J:$J)),
etc..., replacing all references with INDIRECT formula.

The Workbook Daily Output (2006).xls MUST be open

HTH
--
AP

"Martin" <Martin@discussions.microsoft.com> a écrit dans le message de
news:E22806FC-5F07-4E87-AA4D-D2A79A897DFA@microsoft.com...
> I have data from different worksheet according to the week number. I am
using
> If function to look for the data for over different worksheet. My problem
is
> every week there will be a new weeksheet and I have to edit the code to
> include the new week. Is there a simplier solution? Thanks.
>
> =IF(SUMIF('[Daily Output (2006).xls]Week14'!$F:$F,B4,'[Daily Output
> (2006).xls]Week14'!$J:$J),SUMIF('[Daily Output
> (2006).xls]Week14'!$F:$F,B4,'[Daily Output
> (2006).xls]Week14'!$J:$J),IF(SUMIF('[Daily Output
> (2006).xls]Week15'!$F:$F,B4,'[Daily Output
> (2006).xls]Week15'!$J:$J),SUMIF('[Daily Output
> (2006).xls]Week15'!$F:$F,B4,'[Daily Output
> (2006).xls]Week15'!$J:$J),IF(SUMIF('[Daily Output
> (2006).xls]Week16'!$F:$F,B4,'[Daily Output
> (2006).xls]Week16'!$J:$J),SUMIF('[Daily Output
> (2006).xls]Week16'!$F:$F,B4,'[Daily Output
> (2006).xls]Week16'!$J:$J),SUMIF('[Daily Output
> (2006).xls]Week17'!$E:$E,B4,'[Daily Output (2006).xls]Week17'!$I:$I))))