XL2000: Cell References in Copied Formula Are Incorrect (213909)
The information in this article applies to:
This article was previously published under Q213909 SYMPTOMS
When you copy a formula in Microsoft Excel and then paste it into another
cell, the formula that is pasted may not have the correct cell reference or references.
CAUSE
The formula that you paste may have an incorrect cell reference (or references) if the formula that you copied is similar to the following example
and you paste the formula to the left of the original location of the
formula.
Excel always creates a cell range reference in a top-left to
bottom-right fashion. For example, if the formula is in cell C4 and you
copy and paste it to cell B4, because the first cell reference in the
range uses absolute referencing ($B$1), it does not change. But, the
second cell reference in the range uses relative referencing (B3) and performs updates according to the relative location of the destination cell (in this example it should change to A3 because the pasted cell is one cell to the left of the copied cell). In this example, because of the referencing in the original formula, the copied formula has to be:
This behavior runs contrary (top-right to bottom-left) to the way Excel creates range references in a formula, so it is automatically changed to
which lists the range in the default top-left to bottom-right fashion.
NOTE: The cell range is correct, but the absolute references are
incorrect.
RESOLUTION
There is no way to work around this behavior in Microsoft Excel if your
ranges are created as in the example and you paste the formulas to the
left of the original formula. If the formula changes as in the example,
manually modify the range reference so that the absolute references are
correct.
STATUSMicrosoft has confirmed that this is a problem in Excel 2000. REFERENCESFor more information about cell references in formulas, click Microsoft Excel Help on the Help menu, type locate cells referenced by a formula in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type: | Major | Last Reviewed: | 9/27/2003 |
---|
Keywords: | kbbug kbnofix KB213909 |
---|
|