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

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.

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.

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:

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

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