ExcelBanter - Formula help!

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Formula help! (https://www.excelbanter.com/excel-discussion-misc-queries/61403-formula-help.html)

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$65536=--"*2006-01-01*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-06*"))

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$65536=--"*2006-01-09*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-13*"))

so on and so forth.

Thanks for the help in advance! :cool:

--
prhrmk
------------------------------------------------------------------------
prhrmk's Profile: http://www.excelforum.com/member.php...o&userid=23934
View this thread: http://www.excelforum.com/showthread...hreadid=495193

I would suggest a cell reference that you can either change the date in the
one place manual each week, or use the formula "=today()" and the it would
change in the cell automatically.

AB

"prhrmk" wrote:

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$65536=--"*2006-01-01*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-06*"))

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$65536=--"*2006-01-09*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-13*"))

so on and so forth.

Thanks for the help in advance! :cool:

--
prhrmk
------------------------------------------------------------------------
prhrmk's Profile: http://www.excelforum.com/member.php...o&userid=23934
View this thread: http://www.excelforum.com/showthread...hreadid=495193

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$65536=Y1),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=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$65536=INDEX($Y$1:$Y$52,ROWS($1:5)/5)),--('[My
Record
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=INDEX($Z$1:$Z$52,ROWS($1:5)/5)))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

"prhrmk" wrote in
message ...

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$65536=--"*2006-01-01*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-06*"))

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$65536=--"*2006-01-09*"),--('[MyRecord
Log 2006.xls]Rockford-06'!$N$2:$N$65536<=--"*2006-01-13*"))

so on and so forth.

Thanks for the help in advance! :cool:

--
prhrmk
------------------------------------------------------------------------
prhrmk's Profile:
http://www.excelforum.com/member.php...o&userid=23934
View this thread: http://www.excelforum.com/showthread...hreadid=495193