XL2000: Paste Special Transposition Has Unexpected Results (215250)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q215250

SYMPTOMS

If you use a formula to copy cells from another worksheet, and you then use the Paste Special command to transpose those cells, both the formula and the values in the pasted cells change.

WORKAROUND

To work around this issue, you can use the Paste Special command to paste only the values. To paste only the values, click Values under Paste in the Paste Special dialog box.

CAUTION: This workaround breaks the links to the original cells in sheet 1.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you transpose cells, change rows to columns and columns to rows.

The behavior described in the "Symptoms" section of this article occurs if the following conditions are true:
  1. You start Excel, and then create the following spreadsheet on sheet 1 of a new workbook:
       A1: 1
   A2: 2
   A3: 3
  2. You click the Sheet2 tab to open sheet 2.
  3. You select cells A1:A3.
  4. In the formula bar, you type the following equation:

    =SHEET1!A1:A3

  5. You press CTLR+SHIFT+ENTER to enter the formula as an array.

    The cells on sheet 1 are duplicated on sheet 2.
  6. In sheet 2, you select cells A1:A3.
  7. On the Edit menu, you click Copy.
  8. Select cell C3.
  9. On the Edit menu, you click Paste Special.
  10. You click to select the Transpose check box, and then click OK. The values change to zero and the formula also changes.
Modification Type:MajorLast Reviewed:11/5/2003
Keywords:kbbug kbpending KB215250
©2003 Microsoft Corporation. All rights reserved.