XL: Cell References in Copied Formula Incorrect

 Last reviewed: October 7, 1997
Article ID: Q157112
97 WINDOWS kbusage The information in this article applies to:
  • Microsoft Excel 97 for Windows
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel for Windows, versions 5.0, 5.0c
  • Microsoft Excel for the Macintosh, versions 5.0, 5.0a

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. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

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.

KBCategory: kbusage
KBSubcategory: xlformula xlui
Additional reference words: XL97 xl
Keywords : xlformula xlui kbusage
Version : 97
Platform : WINDOWS

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 7, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.