Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copying Formulas
Hmmm.... well I feel stupid, let's just pretend I didn't post this.
Thanks a lot Tom, -- Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying Formulas | Excel Worksheet Functions | |||
COPYING FORMULAS | Excel Worksheet Functions | |||
Copying formulas | Excel Worksheet Functions | |||
Copying Down Formulas Q | Excel Worksheet Functions | |||
Copying formulas | Excel Worksheet Functions |