A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Copying formulas with ranges



 
 
Thread Tools Display Modes
  #1  
Old December 16th, 2003, 08:51 PM
Carla S
external usenet poster
 
Posts: n/a
Default Copying formulas with ranges

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

  #2  
Old December 16th, 2003, 09:25 PM
drabbacs
external usenet poster
 
Posts: n/a
Default Copying formulas with ranges

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

.

  #3  
Old December 18th, 2003, 08:06 PM
Carla S
external usenet poster
 
Posts: n/a
Default Copying formulas with ranges

Thanks. Worked perfectly once I adjusted the appropriate column references to be relative instead of absolute.

Carla

----- 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 Luck



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:22 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.