Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop totals showing from a copied formula? | Excel Discussion (Misc queries) | |||
How do I stop auto-wrap-text? Unclicking in Cells doesnt work... | Excel Discussion (Misc queries) | |||
Stop cells auto-formatting | Excel Discussion (Misc queries) | |||
How to Stop Reformat When Cells Are Copied | Excel Discussion (Misc queries) | |||
How do I stop an Excel file from being copied | Excel Discussion (Misc queries) |