Copying formulas with ranges

C

Carla S

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
 
D

drabbacs

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
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.
 
C

Carla S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top