Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help!
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! -- prhrmk ------------------------------------------------------------------------ prhrmk's Profile: http://www.excelforum.com/member.php...o&userid=23934 View this thread: http://www.excelforum.com/showthread...hreadid=495193 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help!
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! -- prhrmk ------------------------------------------------------------------------ prhrmk's Profile: http://www.excelforum.com/member.php...o&userid=23934 View this thread: http://www.excelforum.com/showthread...hreadid=495193 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula help!
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! -- prhrmk ------------------------------------------------------------------------ prhrmk's Profile: http://www.excelforum.com/member.php...o&userid=23934 View this thread: http://www.excelforum.com/showthread...hreadid=495193 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |