Copying/Pasting Formulas

[SamGB]

Here is the formula i have:

SUMPRODUCT(--('Sheet 1!C1:C10=A7),--('Sheet 1!D110=B7))

I need to paste this formula into 20+ Decending cells...When i copy the
formula down the Cell reference changes i.e A7 goes to A8 etc. which is
my desired result. However the range also increase C1:C10 goes to
C2:C11 which i dont want as it gives false results.

How can you stop the range changing??
Many Thanks

 

[Ken Johnson]

Hi Sam,
type $ before the row numbers in a formula when you want them to be
constant as you fill down. A keyboard shorcut is two presses of the F4
key when you are first entering the formula. F4 once gives $C$1:$C$10,
twice gives C$1:C$10, thrice gives $C1:$C10.
Ken Johnson

 

[dominicb]

Good evening Sam

Change the C1:C10 to $C$1:$C$10. The "dollar" mark will lock either
the rows, or columns, or both depending on which one it appears before.
You don't need to type them in either, just arrow somewhere into the C1
and press F4 - this will cycle through all the options before returning
it to normal.

HTH

DominicB

 

[Guest]

You need to use Absolute Referencing.

C1 is a Relative Reference which will increment when copied.

$C$1 is an Absolute Reference which will remain constant no matter where it
is copied to.

You can also mix the two. $C1 will increment the row but not the column.
C$1 will increment the column, but not the row.

HTH,
Elkar

 

[Rob]

Hi Sam,

When you put a $ in front of the reference you don't want to change then
that will solve it for you.
In this case, do...
SUMPRODUCT(--('Sheet 1!$C$1:$C$10=A7),--('Sheet 1!$D$1:$D$10=B7))

Typing Reference in Excel Help says:
Relative vs. absolute references Depending on the task you want to perform
in Excel, you can use either relative cell references, which are references
to cells relative to the position of the formula, or absolute references,
which are cell references that always refer to cells in a specific location.
If a dollar sign precedes the letter and/or number, such as $A$1, the column
and/or row reference is absolute. Relative references automatically adjust
when you copy them, and absolute references don't.

Rob