| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula Replication
I am trying to replicate a formula down a Column and all I get is an
exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Post the exact formula.
-- Regards, RD ----------------------------------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! ----------------------------------------------------------------------------------------------- "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Is your calculation set to manual? Tools/Options/Calculation - change to
automatic. Or, you could be using absolute references ($A$1 or A$1) instead of relative references (A1). "fredmr" wrote: I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Do you have calculation set to automatic?
Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Thank all three of you for answering.
Calculation ISWAS set to Automatic. Am not using "Absolute" symbolization (like R5C3, NOT $R$5) Example: If I have this equation in R5C4, =IF(SUM(R5C1:R5C3)="","",SUM(R5C1:R5C3)) If I try to replicate/drag that equation down the Column4 to rows 6 thru 12, "R5" stays and does not change to R6, R7, R8..... Could there be something in the "Lock" features of XCELL that I should check?? Again, thanks for your answers, Fred "Don Guillett" wrote: Do you have calculation set to automatic? Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
You need to look at the difference between absolute and relative references.
You've got absolute addresses, as Don suggested. If you're using R1C1 cell references, then if you go into your formula bar and while in your formula select the range R5C1:R5C3, it will, by default, show as RC[-3]:RC[-1], so your formula will become =IF(SUM(RC[-3]:RC[-1])="","",SUM(RC[-3]:RC[-1])) {which are relative references, the equivalent of the cell reference defaulting to not having the $ for an absolute reference in the A1 style}and hence if you drag down it will continue to refer to RC[-3]:RC[-1]. As when using the A1 reference style, if, in the formula bar, you select a cell reference, f4 will toggle between the various absolute and relative references. -- David Biddulph "fredmr" wrote in message ... Thank all three of you for answering. Calculation ISWAS set to Automatic. Am not using "Absolute" symbolization (like R5C3, NOT $R$5) Example: If I have this equation in R5C4, =IF(SUM(R5C1:R5C3)="","",SUM(R5C1:R5C3)) If I try to replicate/drag that equation down the Column4 to rows 6 thru 12, "R5" stays and does not change to R6, R7, R8..... Could there be something in the "Lock" features of XCELL that I should check?? Again, thanks for your answers, Fred "Don Guillett" wrote: Do you have calculation set to automatic? Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Thanks Don,
I really don't understand what you just told me. How would you write that equation or change to what style?? Fred "Don Guillett" wrote: Change from r1c1 style and you will see that you are in absolutes. -- Don Guillett SalesAid Software "fredmr" wrote in message ... Thank all three of you for answering. Calculation ISWAS set to Automatic. Am not using "Absolute" symbolization (like R5C3, NOT $R$5) Example: If I have this equation in R5C4, =IF(SUM(R5C1:R5C3)="","",SUM(R5C1:R5C3)) If I try to replicate/drag that equation down the Column4 to rows 6 thru 12, "R5" stays and does not change to R6, R7, R8..... Could there be something in the "Lock" features of XCELL that I should check?? Again, thanks for your answers, Fred "Don Guillett" wrote: Do you have calculation set to automatic? Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Tools/ Options/ Genera/ Uncheck R1C1 reference style.
Then, if you still don't understand, look at help for absolute and relative cell references. -- David Biddulph "fredmr" wrote in message ... Thanks Don, I really don't understand what you just told me. How would you write that equation or change to what style?? Fred "Don Guillett" wrote: Change from r1c1 style and you will see that you are in absolutes. -- Don Guillett SalesAid Software "fredmr" wrote in message ... Thank all three of you for answering. Calculation ISWAS set to Automatic. Am not using "Absolute" symbolization (like R5C3, NOT $R$5) Example: If I have this equation in R5C4, =IF(SUM(R5C1:R5C3)="","",SUM(R5C1:R5C3)) If I try to replicate/drag that equation down the Column4 to rows 6 thru 12, "R5" stays and does not change to R6, R7, R8..... Could there be something in the "Lock" features of XCELL that I should check?? Again, thanks for your answers, Fred "Don Guillett" wrote: Do you have calculation set to automatic? Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Formula Replication
Thank you all--especially David,
I have done what you (all) told me to do and I resolved my problem. I really appreciate your rapid responses and help. Sorry that I had to be somewhat "dumbo" but I had not worked in Excel since the early Win98 days & I guess I kind of forgot. So, by for now and Thanks again, Fred "David Biddulph" wrote: You need to look at the difference between absolute and relative references. You've got absolute addresses, as Don suggested. If you're using R1C1 cell references, then if you go into your formula bar and while in your formula select the range R5C1:R5C3, it will, by default, show as RC[-3]:RC[-1], so your formula will become =IF(SUM(RC[-3]:RC[-1])="","",SUM(RC[-3]:RC[-1])) {which are relative references, the equivalent of the cell reference defaulting to not having the $ for an absolute reference in the A1 style}and hence if you drag down it will continue to refer to RC[-3]:RC[-1]. As when using the A1 reference style, if, in the formula bar, you select a cell reference, f4 will toggle between the various absolute and relative references. -- David Biddulph "fredmr" wrote in message ... Thank all three of you for answering. Calculation ISWAS set to Automatic. Am not using "Absolute" symbolization (like R5C3, NOT $R$5) Example: If I have this equation in R5C4, =IF(SUM(R5C1:R5C3)="","",SUM(R5C1:R5C3)) If I try to replicate/drag that equation down the Column4 to rows 6 thru 12, "R5" stays and does not change to R6, R7, R8..... Could there be something in the "Lock" features of XCELL that I should check?? Again, thanks for your answers, Fred "Don Guillett" wrote: Do you have calculation set to automatic? Have you removed the absolute references $$$$$ -- Don Guillett SalesAid Software "fredmr" wrote in message ... I am trying to replicate a formula down a Column and all I get is an exact(absolute) copy. I have tried to use the "Drag Handle" and the "Highlight-Edit-Copy-Drag/Highlight-Paste" functions but All I get is a "Copy" with no updating of the "Row" information. Any suggestions along this line would be helpful. Thanks in advance, Fred PS: I am using Xcel 2003 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
