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

Article ID: 186370

Article Last Modified on 10/22/2000

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

MORE INFORMATION

Example

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

Start Microsoft Excel 97 and create a new workbook.
Select $A$1:$A$2.
Type =ROW()*1 and press CTRL+ENTER.
On the Edit menu, click Cut.
Select $F$10.
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.

REFERENCES

For more information about the ROW worksheet function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

ROW worksheet function

and then double-click the selected text to go to the "ROW" topic. If you are unable to find the information you need, ask the Office Assistant.

For more information about the COLUMN worksheet function, click Contents And Index on the Help menu, click the Index tab in Microsoft Excel Help, type the following text

COLUMN worksheet function

and then double-click the selected text to go to the "COLUMN" topic. If you are unable to find the information you need, ask the Office Assistant.

Additional query words: XL97 recalc recalculated

Keywords: kbbug kbfix KB186370