Stop auto modify of copied cells
 

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.

Stop auto modify of copied cells
 

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

"Eric" wrote in message
...
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.

Stop auto modify of copied cells
 

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" wrote:

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

"Eric" wrote in message
...
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.

Stop auto modify of copied cells
 

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

Regards,
Paul

"Eric" wrote in message
...
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" wrote:

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

"Eric" wrote in message
...
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.