Here's my situation: I'm writing formulas into an excel template from sas using dde but there are three tabs in the worksheet and there are a lot of columns on two of the tabs that will need a sumif statement. The problem I've encountered is the range and sum_range of the sumif statement are from different workbooks than where the actual sumif calculation is. I've found a temporary solution but it will require a lot of steps and will undoubtedly make my code massive. Please take a look and let me know if anyone has any suggestions. If someone can help me figure out how to write the sum if statement so that it's similar to "put '[formula.fill("=1+1","R2C4:R20C4")]';" it would certainly make my day! At least using this code, I would be able to tell it the formula range without an added step. I've tried everything. Thanks!
Here's my code thus far:
filename ddedata1 dde 'excel|G:\MY DOCUMENTS\[template dc10.xlt]By Store!R3C12:R4410C12' notab lrecl=4412;
data _null_;
file ddedata1;
put '=sumif(data!$c:$c,$a3,data!L:L)';
run;
quit;
data _null_;
file ddeopen;
put '[error(false)]';
put '[workbook.activate("By Store")]';
put '[select("R3C12")]';
put '[copy("R3C12")]';
put '[paste("R4C12:R4410C12")]';
run;
quit;
For just this section of my template that I'd need to fill in with these formulas, I'd have to copy and paste it 31 more times and that's just for one of the two workbooks.
