software

I am using the SUMPRODUCT formula which is working great - however, when
I copy and past it I have to manually change the dates which is going to
take me a lot of time to do so. I want a formula that will do this for
me when I copy it. If that makes sense? I have bolded the parts that
need to change.

=SUMPRODUCT(--('[MyRecord Log
2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-01*quot;),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-06*quot;))

Is there a formula - like OFFSET that would do this for me otherwise I
have to change the dates manually. The report is done on a weekly
basis so the dates would be the following:

=SUMPRODUCT(--('[MyRecord Log
2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-09*quot;),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-13*quot;))

so on and so forth.

Thanks for the help in advance! --
prhrmk
------------------------------------------------------------------------
prhrmk's Profile: www.excelforum.com/member.php...oamp;userid=23934
View this thread: www.excelforum.com/showthread...hreadid=495193how about using a cell reference and copy down--
Don Guillett
SalesAid Software

quot;prhrmkquot; gt; wrote in
message news
gt;
gt; I am using the SUMPRODUCT formula which is working great - however, when
gt; I copy and past it I have to manually change the dates which is going to
gt; take me a lot of time to do so. I want a formula that will do this for
gt; me when I copy it. If that makes sense? I have bolded the parts that
gt; need to change.
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-01*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-06*quot;))
gt;
gt; Is there a formula - like OFFSET that would do this for me otherwise I
gt; have to change the dates manually. The report is done on a weekly
gt; basis so the dates would be the following:
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-09*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-13*quot;))
gt;
gt; so on and so forth.
gt;
gt; Thanks for the help in advance!
gt;
gt;
gt; --
gt; prhrmk
gt; ------------------------------------------------------------------------
gt; prhrmk's Profile:
gt; www.excelforum.com/member.php...oamp;userid=23934
gt; View this thread: www.excelforum.com/showthread...hreadid=495193
gt;
I would suggest a cell reference that you can either change the date in the
one place manual each week, or use the formula quot;=today()quot; and the it would
change in the cell automatically.

AB

quot;prhrmkquot; wrote:

gt;
gt; I am using the SUMPRODUCT formula which is working great - however, when
gt; I copy and past it I have to manually change the dates which is going to
gt; take me a lot of time to do so. I want a formula that will do this for
gt; me when I copy it. If that makes sense? I have bolded the parts that
gt; need to change.
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-01*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-06*quot;))
gt;
gt; Is there a formula - like OFFSET that would do this for me otherwise I
gt; have to change the dates manually. The report is done on a weekly
gt; basis so the dates would be the following:
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-09*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-13*quot;))
gt;
gt; so on and so forth.
gt;
gt; Thanks for the help in advance!
gt;
gt;
gt; --
gt; prhrmk
gt; ------------------------------------------------------------------------
gt; prhrmk's Profile: www.excelforum.com/member.php...oamp;userid=23934
gt; View this thread: www.excelforum.com/showthread...hreadid=495193
gt;
gt;

One way might be to place your dates in a datalist, and reference those
cells in your formula.

Y1 to Y52
1/2/06
1/9/06
1/16/06
.... etc.

Z1 to Z52
1/6/06
1/13/06
1/20/06
.... etc.

Then, your formula would be:

=SUMPRODUCT(--('[MyRecord Log
2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
2006.xls]Rockford-06'!$N$2:$N$65536gt;=Y1),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=Z1))

However, if you're *not* copying down sequential rows, but skipping a few,
say copying into every 5th row, 1, 6, 11, 16, ... etc., you could try
something like this with the same datalist in Y1 to Z52:

=SUMPRODUCT(--('[MyRecord Log
2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
2006.xls]Rockford-06'!$N$2:$N$65536gt;=INDEX($Y$1:$Y$52,ROWS($1:5)/5)),--('[My
Record
Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=INDEX($Z$1:$Z$52,ROWS($1:5)/5)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================quot;prhrmkquot; gt; wrote in
message news
gt;
gt; I am using the SUMPRODUCT formula which is working great - however, when
gt; I copy and past it I have to manually change the dates which is going to
gt; take me a lot of time to do so. I want a formula that will do this for
gt; me when I copy it. If that makes sense? I have bolded the parts that
gt; need to change.
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-01*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-06*quot;))
gt;
gt; Is there a formula - like OFFSET that would do this for me otherwise I
gt; have to change the dates manually. The report is done on a weekly
gt; basis so the dates would be the following:
gt;
gt; =SUMPRODUCT(--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$L$2:$L$65536),--('[MyRecord Log
gt; 2006.xls]Rockford-06'!$N$2:$N$65536gt;=--quot;*2006-01-09*quot;),--('[MyRecord
gt; Log 2006.xls]Rockford-06'!$N$2:$N$65536lt;=--quot;*2006-01-13*quot;))
gt;
gt; so on and so forth.
gt;
gt; Thanks for the help in advance!
gt;
gt;
gt; --
gt; prhrmk
gt; ------------------------------------------------------------------------
gt; prhrmk's Profile:
www.excelforum.com/member.php...oamp;userid=23934
gt; View this thread: www.excelforum.com/showthread...hreadid=495193
gt;