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))))

How to simplfy If & Sumif Code
 

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" a écrit dans le message de
...
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))))