Copying Formulas
 

I'm trying to copy a long formula from Cell "F3" down to "F500" but I
only want some of the references to change. Here's my formula in
standard and R1C1 notation (sorry about the word wrapping):

{=IF(B31,SUMPRODUCT(--($C$3=TWC!B4:B999),--($E$3=TWC!A4:A999),--(TWC!
C4:C999)),"")}


FormulaArray = _
"=IF(RC[-4]1,SUMPRODUCT(--(R5C3=TWC!R[-1]C[-4]:R[994]C[-4]),--
(R5C5=TWC!R[-1]C[-5]:R[994]C[-5]),--(TWC!
R[-1]C[-3]:R[994]C[-3])),"""")"


Here's how the formula would look in "F500":
{=IF(B5001,SUMPRODUCT(--($C$500=TWC!B4:B999),--($E$500=TWC!A4:A999),--
(TWC!C4:C999)),"")}


Can I put an integer variable (or an offset) inside the formula where
I want the references to change and then just run it down the column?
Or is there a better way to do it?

Thanks,
-- Dan

Copying Formulas
 

You have your $ signs backwards. $ signs fix the reference. This should work:

=IF($B31,SUMPRODUCT(--($C3=TWC!$B$4:$B$999),--($E3=TWC!$A$4:$A$999),--(TWC!$C$4:$C$999)),"")


--
Regards,
Tom Ogilvy





"Dan R." wrote:

I'm trying to copy a long formula from Cell "F3" down to "F500" but I
only want some of the references to change. Here's my formula in
standard and R1C1 notation (sorry about the word wrapping):

{=IF(B31,SUMPRODUCT(--($C$3=TWC!B4:B999),--($E$3=TWC!A4:A999),--(TWC!
C4:C999)),"")}


FormulaArray = _
"=IF(RC[-4]1,SUMPRODUCT(--(R5C3=TWC!R[-1]C[-4]:R[994]C[-4]),--
(R5C5=TWC!R[-1]C[-5]:R[994]C[-5]),--(TWC!
R[-1]C[-3]:R[994]C[-3])),"""")"


Here's how the formula would look in "F500":
{=IF(B5001,SUMPRODUCT(--($C$500=TWC!B4:B999),--($E$500=TWC!A4:A999),--
(TWC!C4:C999)),"")}


Can I put an integer variable (or an offset) inside the formula where
I want the references to change and then just run it down the column?
Or is there a better way to do it?

Thanks,
-- Dan

Copying Formulas
 

Hmmm.... well I feel stupid, let's just pretend I didn't post this.

Thanks a lot Tom,
-- Dan