Copying Formulas

D

Dan R.

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(B3>1,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(B500>1,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
 
T

Tom Ogilvy

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

=IF($B3>1,SUMPRODUCT(--($C3=TWC!$B$4:$B$999),--($E3=TWC!$A$4:$A$999),--(TWC!$C$4:$C$999)),"")
 
D

Dan R.

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

Thanks a lot Tom,
-- Dan
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top