C
Carla S
I'm trying to find out if there is an easier way to copy a bunch of formulas that work with arrays/ranges to other cells withiut having to manually update all the cell references by hand.
Here's an example:
In cell D7 on Sheet2 I have the following formula:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$L$7:$P$7,ROW('Sheet1'!$L$7:$L$222)-ROW('Sheet1'!$L$7),0),{"x",50}),{1;1})>0)+0)
In D8 the formula is:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$R$7:$V$7,ROW('Sheet1'!$R$7:$R$222)-ROW('Sheet1'!$R$7),0),{"x",50}),{1;1})>0)+0)
These formulas are for calculating summary statistics by week. Sheet1 contains columns for each workday and a blank column to separate each week.
The row ranges are static but the column references need to change to pick up the correct range. When I copy the formula in D7 to D8 with relative column references, I get a range of M:Q instead of the desired R:V, as in the example below:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!M$7:Q$7,ROW('Sheet1'!M$7:M$222)-ROW('Sheet1'!M$7),0),{"x",50}),{1;1})>0)+0)
I'm guessing there may be away to use "Indirect" but I can't seem to get it working!!!
Here's another example of another types of formulas in my sheet (in cell E8 on Sheet2) that I'd want to copy/paste in the same way:
=(COUNTIF('Sheet1'!$R$7:$V$222,"x")/5)+(COUNTIF('Sheet1'!$R$7:$V$222,50)/10)
I appreciate any suggestions on how to ease this copy/paste nightmare!
Thanks,
Carla S
Here's an example:
In cell D7 on Sheet2 I have the following formula:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$L$7:$P$7,ROW('Sheet1'!$L$7:$L$222)-ROW('Sheet1'!$L$7),0),{"x",50}),{1;1})>0)+0)
In D8 the formula is:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$R$7:$V$7,ROW('Sheet1'!$R$7:$R$222)-ROW('Sheet1'!$R$7),0),{"x",50}),{1;1})>0)+0)
These formulas are for calculating summary statistics by week. Sheet1 contains columns for each workday and a blank column to separate each week.
The row ranges are static but the column references need to change to pick up the correct range. When I copy the formula in D7 to D8 with relative column references, I get a range of M:Q instead of the desired R:V, as in the example below:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!M$7:Q$7,ROW('Sheet1'!M$7:M$222)-ROW('Sheet1'!M$7),0),{"x",50}),{1;1})>0)+0)
I'm guessing there may be away to use "Indirect" but I can't seem to get it working!!!
Here's another example of another types of formulas in my sheet (in cell E8 on Sheet2) that I'd want to copy/paste in the same way:
=(COUNTIF('Sheet1'!$R$7:$V$222,"x")/5)+(COUNTIF('Sheet1'!$R$7:$V$222,50)/10)
I appreciate any suggestions on how to ease this copy/paste nightmare!
Thanks,
Carla S