XL2000: Formula References Are Incorrect After You Press CTRL+ENTER (214374)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q214374

SYMPTOMS

In Microsoft Excel 2000, if you enter a formula in a range of cells by selecting the cells, typing the formula, and pressing CTRL+ENTER, cell references in the formulas may switch to absolute instead of relative referencing, or vice-versa. Also, in a cell reference, designations for two cells may switch rows or columns (for example, the range B7:D5 may appear as B5:D7).

This may cause additional problems if you then fill the formulas, or if you insert or delete rows or columns in a worksheet.

CAUSE

These problems may occur when you apply the formula to all of the cells, and the formula contains a backward reference to a range of cells. A backward reference is one in which the second cell in the reference is either above or to the left of the first cell.

NOTE: If you do not use the same combination of absolute and relative cell referencing for both cells in the formula, the problem becomes apparent when you fill the formulas or enter them in a range of cells by pressing CTRL+ENTER.

WORKAROUND

To work around this problem, use the same combination of absolute and relative referencing for both cells that are referenced in the formula.

For example, the following combinations of absolute and relative referencing do not cause the problem to occur: 
   $B$5:$B$10
   B5:B10
   B$5:B$10
   $B5:$B10
Any other combinations that use a mixture of absolute and relative referencing (for example, B5:$B$10) may cause the problem to occur.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

The following examples demonstrate the problems described in the Symptoms section of this article.

Example 1

  1. In a new worksheet, select the range A1:A2, type the following formula

    =SUM(B1:$B$1)

    and then press CTRL+ENTER.
  2. Note the information about cells A1 and A2 that is listed in the following table.
                                                 Referencing
          Expected        Formula that       correct or
   Cell   formula         actually appears   incorrect
   ----------------------------------------------------
   A1     =SUM(B1:$B$1)   =SUM(B1:$B$1)      Correct
   A2     =SUM(B2:$B$1)   =SUM(B$1:$B2)      Incorrect
    When the formula is applied to cell A2, the second cell, B1, is above the first cell, B2. When Microsoft Excel switches the two cells, it incorrectly applies absolute and relative referencing to the formula. However, the formula does refer to the correct cells.

    If you enter the following formula instead

    =SUM($B$1:B1)

    both of the formulas in cells A1 and A2 are correct.

Example 2

  1. In a new worksheet, select the range A1:C3, and type the following formula

    =SUM(E3:$F$4)

    and then press CTRL+ENTER.
  2. Note the information about cells A1:C3 that is listed in the following table.
                                                 Referencing
          Expected        Formula that       correct or
   Cell   formula         actually appears   incorrect
   ----------------------------------------------------
   A1     =SUM(E3:$F$4)   =SUM(E3:$F$4)      Correct
   A2     =SUM(E4:$F$4)   =SUM(E4:$F$4)      Correct
   B1     =SUM(F3:$F$4)   =SUM(F3:$F$4)      Correct
   B2     =SUM(F4:$F$4)   =SUM(F4:$F$4)      Correct
   C3     =SUM(G5:$F$4)   =SUM($F$4:G5)      Correct

   B3     =SUM(F5:$F$4)   =SUM(F$4:$F5)      Incorrect
   C2     =SUM(G4:$F$4)   =SUM($F4:G$4)      Incorrect
   A3     =SUM(E5:$F$4)   =SUM(E$4:$F5)      Incorrect
   C1     =SUM(G3:$F$4)   =SUM($F3:G$4)      Incorrect
    When the formula is applied to cells A3, B3, C1, C2, and C3, the second cell in each reference is above or to the left of the first cell. When Microsoft Excel switches the cells, it incorrectly applies absolute and relative referencing to the formula.

    Cells in the formulas in cells A3 and C1 have also switched row numbers; instead of cell F4, the formulas in A3 and C1 refer to cells F5 and F3. The ranges that are referenced by the formulas are correct even though the row numbers are switched.

    In addition, the formula in cell C3 appears correctly even though the second cell, F4, is both above and to the left of the first cell, G5.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kbpending KB214374
©2003 Microsoft Corporation. All rights reserved.