XL5: Cut/Paste Formula with Reference Returns #REF! Error

ID: Q127791

The information in this article applies to:

Microsoft Excel for Windows, versions 5.0, 5.0c
Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel, if you cut a range of cells that contains both a formula and the cells that it refers to, and you paste the formula and the cells to another worksheet, the reference in the formula is displayed as the #REF! error value.

This error value may cause the resulting formula to return the #REF! error value as well.

CAUSE

This behavior occurs if you use the Cut command to move a formula from one worksheet to another worksheet when the formula contains a reference to a cell on the original worksheet. For example, if you cut the cells A1:A4, and if cell A4 contains the following formula

   =SUM(A1:A3)

if you paste A1:A4 to a cell on another worksheet, the formula appears as =SUM(#REF!) and returns the #REF! error value on the worksheet.

WORKAROUND

To avoid receiving the #REF! error value when you move a formula to another worksheet, use the Copy command instead of the Cut command by doing the following:

1. Select the cell that contains the formula that you want to move.

2. On the Edit menu, click Copy.

3. Select the worksheet to which you want to move the formula.

4. Select the destination cell, and click Paste on the Edit menu.

NOTE: If the formula uses relative references, its new location must have at least the same number of cells above it as the original location. For example, if you copy a formula from cell A4 on Sheet1, and if that formula references cells A1:A4, the destination location must have at lease four cells above it.

5. On the original worksheet, delete the formula that you copied.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel version 5.0a for the Macintosh and Microsoft Excel for Windows 95, version 7.0.

NOTE: If you cut a formula that contains relative references to cells on a worksheet (and you do not cut the cells that the formula refers to) and you paste that formula to another worksheet, the formula is not adjusted and you will always receive a #REF! error value. This is a limitation of Microsoft Excel.

REFERENCES

For more information about Moving Cells, or the Cut Command (Edit Menu), choose the Search button in MS Excel Help and type:

   moving cells

Additional query words: 5.00 5.00a 5.00c

Keywords          : xlformula 
Version           : WINDOWS:5.0,5.0c; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbbug

Last Reviewed: February 5, 1998