Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Martin
 
Posts: n/a
Default 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))))
  #2   Report Post  
Posted to microsoft.public.excel.misc
Ardus Petus
 
Posts: n/a
Default 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))))



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumif of Sumif perhaps? Fred Excel Discussion (Misc queries) 2 March 29th 06 05:39 PM
Need 2 add second then third code with first code in the Tab View nick s Excel Worksheet Functions 3 December 6th 05 02:20 AM
Conform a total to a list of results? xmaveric Excel Discussion (Misc queries) 1 August 21st 05 07:22 PM
SumIF function ACDenver Excel Discussion (Misc queries) 2 August 17th 05 09:47 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"