Range Change

[CARBOB]

I'm stuck, in column ST20 is a formula that uses data from columns H & I. I need to drag the formula across the sheet for 300 columns with the range changing for each column to the next 2 columns J&K,L&M,N&O etc. When I drag across, the range changes from H&I to I&J, (this needs to be J&K). Is there any way to setup Excel to do this?

Excel Workbook
	ST	SU	SV
19	7.59	9.80	7.14
20	1	0	0
5 DIGIT PAIRS

Excel 2007

Cell Formulas
Range	Formula
ST19	=COUNT($A:$A)/ST16
SU19	=COUNT($A:$A)/SU16
SV19	=COUNT($A:$A)/SV16

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
pair	=#REF!#REF!
Workbook Defined Names

 

[John Davis]

I'm stuck, in column ST20 is a formula that uses data from columns H & I. I need to drag the formula across the sheet for 300 columns with the range changing for each column to the next 2 columns J&K,L&M,N&O etc. When I drag across, the range changes from H&I to I&J, (this needs to be J&K). Is there any way to setup Excel to do this?

Excel Workbook
	ST	SU	SV
19	7.59	9.80	7.14
20	1	0	0
5 DIGIT PAIRS

Excel 2007

Cell Formulas
Range	Formula
ST19	=COUNT($A:$A)/ST16
SU19	=COUNT($A:$A)/SU16
SV19	=COUNT($A:$A)/SV16

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Excel Workbook
Name	Refers To
pair	=#REF!#REF!
Workbook Defined Names

You could use the Offset command with a helper row for reference. It's a bit hard for me to explain though.

Say you use row 2 as the helper row. In H2 and I2 place the value 0. In J2 place "=h2+2" in K2 "=I2+2". then copy and paste the formulas in J2 and K2 across as far as you need.

Now in a second helper row (row 3). Use your Offset function.

H3 =OFFSET($H$1,0,H2)
I3 =OFFSET($I$1,0,I2)

Now instead of referencing H1 and I1 you reference H3 and I3? I know it's confusing, and there are probably many better ways, but that's my shot at it.

 

[CARBOB]

Thank you for your response, will give it a shot!!