Stop auto modify of copied cells

[Guest]

I am not sure if I have the term correctly but what I am trying to do is copy
a cell's formula down 20 or so rows, but when I copy them they modify the
formulas so that they correspond with the row they are on.
Here is one of my formulas:
=SUMPRODUCT((Data!A10:A300<=2)*(Data!C10:C300=A6))

When I paste it into the rows below it (by dragging the little black box in
the bottom right of the original cell) it modifies "Data!A10:A300" to
"Data!A11:A301" and it changes "A6" to "A7."
The second change I do want to occur, but I don't want the modification of
where it is getting the data to change.

To state it again, if I look at the formulas for each cell in a colum I want
them to look like this:

=SUMPRODUCT((Data!A10:A300<=2)*(Data!C10:C300=A6))
=SUMPRODUCT((Data!A10:A300<=2)*(Data!C10:C300=A7))
=SUMPRODUCT((Data!A10:A300<=2)*(Data!C10:C300=A8))
etc...etc...

But when I do them now, they look like this:
=SUMPRODUCT((Data!A10:A300<=2)*(Data!C10:C300=A6))
=SUMPRODUCT((Data!A11:A301<=2)*(Data!C11:C301=A7))
=SUMPRODUCT((Data!A12:A302<=2)*(Data!C12:C302=A8))

I have autoformat turned off, autocorrect turned off...
This is for excel 2003, SP2.

Thank you for your time and help.

 

[PCLIVE]

Use $ to maintain a constant position.

Data!A$10:A$300

This maintains the rows specified even when copied down. To maintain the
column as well:
Data!$A$10:$A$300

HTH,
Paul

 

[Guest]

Thank you Paul, but I have found that if a row is deleted then the function
still changes even if $ is preceding it.

So if I go and delete a row that contains the data the function will then
look like this:
=SUMPRODUCT((Data!A10:A299<=2)*(Data!C10:C299=A6))
Is there a way to keep this from happening with deletions?

Thanks for your time and help.

 

[PCLIVE]

I'm not sure you can get around that without some VB Code. Perhaps someone
else has a suggestion.

Regards,
Paul