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.

I'm using Excel 2002.

Thanks,
Carla

.