Cell References in Copied Formula Incorrect (157112)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
This article was previously published under Q157112 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(s).
CAUSE
The formula that you paste may have the incorrect cell references if the
formula you copied is similar to the following example
=SUM($B$1:B3)
and you paste the formula to the left of the original location of the
formula.
Microsoft 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 will not change. But, the
second cell reference in the range uses relative referencing (B3)
and will update 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 would
have to be:
=SUM($B$1:A3)
This is contrary (top-right, bottom-left fashion) to the way Microsoft
Excel creates range references in a formula, so it is automatically
changed to
=SUM(A$1:$B3)
which lists the range in the default top-left, 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 the absolute references are
correct.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article.
REFERENCES
For more information about Cell References in Formulas, click the Index
tab in Microsoft Excel 97 Help, type the following text
formulas, cell references
and then double-click the selected text to go to the "About cell and range
references" topic.
| Modification Type: | Minor | Last Reviewed: | 8/17/2005 |
|---|
| Keywords: | KB157112 |
|---|
|