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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|