Microsoft KB Archive/172791

= XL: Formula References Incorrect After Pressing CTRL+ENTER =

Article ID: 172791

Article Last Modified on 8/17/2005

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



This article was previously published under Q172791





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

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 this to be a problem in the Microsoft products listed at the beginning of this article.



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) and then press CTRL+ENTER.

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.

Note that if you enter the following formula instead =SUM($B$1:B1) both of the formulas in cells A1 and A2 are correct.

Example 2
In a new worksheet, select the range A1:C3, type the following formula =SUM(E3:$F$4) and then press CTRL+ENTER.

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.

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.

Additional query words: XL97

Keywords: kbprb KB172791

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.