Microsoft KB Archive/214374

= XL2000: Formula References Are Incorrect After You Press CTRL+ENTER =

Article ID: 214374

Article Last Modified on 11/5/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



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

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, and type the following formula

=SUM(E3:$F$4)

and then press CTRL+ENTER.</li>  Note the information about cells A1:C3 that is listed in the following table. <pre class="fixed_text">                                            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. </li></ol>

Additional query words: XL2000

Keywords: kbbug kbpending KB214374

-

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

© Microsoft Corporation. All rights reserved.