XL: Formula References Incorrect After Pressing CTRL+ENTER

• Microsoft Excel 97 for Windows
• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c

SYMPTOMS

In Microsoft Excel, 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 be switched 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 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 are not using 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

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.

MORE INFORMATION

The following examples demonstrate the problems described in the "Symptoms" section.

Example 1

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

   =SUM(B1:$B$1)

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

Note that if you enter the following formula instead

   =SUM($B$1:B1)

Example 2

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

   =SUM(E3:$F$4)

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

Note that 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.

Note also that 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.