Copying formulas with ranges

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to set up several formulas that calculate totals based on ranges/arrays so that they can be copied to adjacent columns without someone having to update the column references manually. I had another post here about a week ago and received no responses. I thought I'd try to explain it another way one more time to see if anyone may be able to help.

For example, I have formulas on Sheet2 that perform calculations on series of ranges on Sheet1 such as $A$7:$E$222, then $G7:$K$222, etc. Each range is the same size (5 columns wide by 215 rows deep) and is separated by a "blank" column.

In Sheet2 I'd like to paste the formulas from column A into the columns to the immediate right such that column A works on range A:E from Sheet1 while column B (which would contain the copied formulas from column A) would work on range G:K from Sheet1, then column C formulas woul work on range M:Q from Sheet1 and so on.

Here's a couple of the actual formulas I have to make this work with:
In cell sheet C8:
=(COUNTIF('Sheet1'!$F$7:$J$222,"x")/4)+(COUNTIF('Sheet1'!$F$7:$J$222,50)/8)
In cell sheet C7:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$F$7:$J$7,ROW('Sheet1'!$F$7:$F$222)-ROW('Sheet1'!$F$7),0),{"x",50}),{1;1})>0)+0)

When copied to column D of Sheet2, these formulas would have to work on range L7:P222, etc.

Any help on making these formulas more generic to make copying and pasting easier (without having to manually update everything) would be much appreciated.

I'm using Excel 2002.

Thanks,
Carla
 
This trick should work.

Since you want your formulas to work on ranges A-E, G-H, M-
Q, etc What you do is write your formula(s) in column A
and then copy/paste into columns G, M, S etc. In other
words, paste in every 6th column (or whatever spacing).
Then select the blank columns between and delete. Voila.

Good Luck



-----Original Message-----
I'm trying to set up several formulas that calculate
Click to expand...
totals based on ranges/arrays so that they can be copied
to adjacent columns without someone having to update the
column references manually. I had another post here about
a week ago and received no responses. I thought I'd try to
explain it another way one more time to see if anyone may
be able to help.
For example, I have formulas on Sheet2 that perform
Click to expand...
calculations on series of ranges on Sheet1 such as
$A$7:$E$222, then $G7:$K$222, etc. Each range is the same
size (5 columns wide by 215 rows deep) and is separated by
a "blank" column.
In Sheet2 I'd like to paste the formulas from column A
Click to expand...
into the columns to the immediate right such that column A
works on range A:E from Sheet1 while column B (which would
contain the copied formulas from column A) would work on
range G:K from Sheet1, then column C formulas woul work on
range M:Q from Sheet1 and so on.
Here's a couple of the actual formulas I have to make this work with:
In cell sheet C8:
=(COUNTIF('Sheet1'!$F$7:$J$222,"x")/4)+(COUNTIF('Sheet1'! $F$7:$J$222,50)/8)
In cell sheet C7:
=SUMPRODUCT((MMULT(COUNTIF(OFFSET('Sheet1'!$F$7:$J$7,ROW ('Sheet1'!$F$7:$F$222)-ROW('Sheet1'!$F$7),0),{"x",50}),
{1;1})>0)+0)

When copied to column D of Sheet2, these formulas would
Click to expand...
have to work on range L7:P222, etc.
Any help on making these formulas more generic to make
Click to expand...
copying and pasting easier (without having to manually
update everything) would be much appreciated.
 
Thanks. Worked perfectly once I adjusted the appropriate column references to be relative instead of absolute

Carl

----- drabbacs wrote: ----

This trick should work

Since you want your formulas to work on ranges A-E, G-H, M
Q, etc What you do is write your formula(s) in column A
and then copy/paste into columns G, M, S etc. In other
words, paste in every 6th column (or whatever spacing).
Then select the blank columns between and delete. Voila

Good Luc
 
Back
Top