Microsoft KB Archive/186370

= XL97: ROW or COLUMN Formula Is Not Updated After Cut and Paste =

Article ID: 186370

Article Last Modified on 10/22/2000

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition

-



This article was previously published under Q186370





SYMPTOMS
When you perform a cut-and-paste operation to move a range of formulas that contains the ROW or COLUMN function, the formulas may not be updated.



CAUSE
This problem occurs when the following conditions are true:


 * You press CTRL+ENTER to enter a formula in multiple cells. -and-


 * The formula contains the ROW or COLUMN function. -and-


 * The formula contains some operator (for example "+") or is nested in other functions (for example "=INT(ROW)") so that the formula becomes a "shared" formula. -and-


 * You move the range of formulas to another location on the worksheet.



RESOLUTION
To prevent this problem, obtain and install Microsoft Office 97, Service Release 2 (SR-2).

For additional information about SR-2, please see the following article in the Microsoft Knowledge Base:

151261 OFF97: How to Obtain and Install MS Office 97 SR-2



STATUS
Microsoft has confirmed this to be a problem in Microsoft Excel 97 for Windows. This problem was corrected in Microsoft Excel 97 for Windows, Service Release 2 (SR-2).



Example
To see an example of the problem that is described in this article, follow these steps:


 * 1) Start Microsoft Excel 97 and create a new workbook.
 * 2) Select $A$1:$A$2.
 * 3) Type =ROW*1 and press CTRL+ENTER.
 * 4) On the Edit menu, click Cut.
 * 5) Select $F$10.
 * 6) On the Edit menu, click Paste.

Note that the formulas in $F$10:$F$11 are not recalculated.

What Are Shared Formulas?
In Microsoft Excel, if a worksheet contains a sequence of cells that contain formulas or values that follow a distinct pattern, the formulas or values may be shared, for example, if a worksheet contains the following formulas and values:

A1: =E1+F1

A2: =E2+F2

A3: =E3+F3

A4: =E4+F4

A5: =E5+F5

A6: =E6+F6

Note the pattern in the formulas in column A. Except for row numbers that increase by one in each row, each formula is identical to the formula in the row above it. Because of this pattern, the formulas in column A are shared formulas.

You can quickly place the formula in column A by selecting the range and pressing CTRL+ENTER.

