Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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)))) |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
Need 2 add second then third code with first code in the Tab View | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
SumIF function | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |