XL2000: Linked Formula May Return a Maximum of 255 Characters

When you open a Microsoft Excel 2000 workbook (the dependent workbook) that contains formulas that link to cells in another workbook (the source workbook), the formulas may return a maximum of 255 characters, even though the cells in the source workbook contain more than 255 characters.

This behavior can occur if both of the following conditions are true:

• You fail to open the source workbook.
• Cells in the source workbook contain more than 255 characters.

To work around this issue, open the source workbook, either before or after you open the dependent workbook. When you do this, the formulas in the dependent workbook return the correct number of characters.

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

Worksheet cells can contain a maximum of 32,767 characters. This is different from versions of Excel before Excel 97, in which worksheet cells can contain a maximum of 255 characters. Even though cells can contain a very large number of characters, you may experience problems when working with these cells.

Example

In the following example, the formulas in the dependent workbook return a maximum of 255 characters:

1. Create a new workbook.
2. In cell A1 of Sheet1, type the following formula:
   =REPT("x",5)
   Note that the formula returns five "x" characters ("xxxxx").
3. On the File menu, click Save As, and then save the workbook as Book1.xls in the My Documents folder.
4. Create a second new workbook.
5. Type the following formulas in Sheet1:
   A1: =[Book1.xls]Sheet1!A1
   A2: =LEN(A1)
   Note that the formula in cell A1 also returns 5 "x" characters and the formula in cell A2 returns the value 5.
6. On the File menu, click Save, and then save the workbook as Book2.xls in the My Documents folder. On the File menu, click Close.
7. In Book1.xls, change the formula in cell A1 to the following:
   =REPT("x",32767)
   The formula returns 32,767 "x" characters.
8. On the File menu, click Save, and then click Close.
9. Open Book2.xls. When you are asked to update links, click Yes.
   
   The formula in cell A1 returns 255 "x" characters, not 32,767 characters. Also, the formula in cell A2 returns the value 255.
10. Open Book1.xls, and then switch to Book2.xls.
    
    The formula in cell A1 returns 32,767 "x" characters, and the formula in cell A2 returns the value 32767.