MXL5: Links Incorrect When Source Document(s) Closed

ID: Q122464

The information in this article applies to:

Microsoft Excel for the Macintosh, version 5.0

SYMPTOMS

In Microsoft Excel version 5.0 for the Macintosh, linked cells on your worksheet may display incorrect data. The value displayed may be zero, a very large number, or a very small number instead of the actual data.

CAUSE

This problem occurs only if the source document is closed. The problem occurs in the following cases:

Formatted Blank Cells

When the cell that you link to is preceded by multiple blank cells, and the blank cells contain some type of formatting, such as bold, the linked cell contains an incorrect value if the source document is closed.

For example, when you link to cell G1 in the following example, if cells C1:F1 contain formatting, this behavior occurs:

   A1:   B1: 23  C1:   D1:   E1:   F1:   G1: 4

Series of Integer and/or Simple Numeric Values

When the linked worksheet contains a row of integer or simple numeric values (1 or 2 decimal places), and you link to a cell in the same row that is preceded by a series of these numbers which is not an integer or simple numeric value (has more decimal places or is a formula), the linked cell contains an incorrect value if the source document is closed.

Take for example a worksheet where column A contains text entries describing the data, columns B-F contain numeric values with 2 decimal places (dollar values), and column G contains the sums of the data. When you link to any cell in column G, the incorrect value is displayed when the source document is closed.

Note that these are not all of the causes of this problem. There are other scenarios that may also cause this problem.

WORKAROUNDS

To work around this problem, use any of the following methods.

Method 1

Open the source files. This causes the linked cells to display the correct data.

Method 2

In the case of formatted blank cells, open the source worksheet, and follow these steps:

1. On the Edit menu, click Go To. Click Special.

2. Under Select, select the Blanks option, and click OK to select all of

   the blank cells.

3. On the Edit menu, point to Clear, and click Formats.

Method 3

Move all of the linked worksheets into one workbook.

Method 4

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/supportnet/refguide/

In the case of integer or simple numeric values, open the source file, select every other column of the rows that contain numeric data and replace the values with formulas. You can do this by adding an equal sign (=) at the beginning of each cell that contains a numeric value.

 You can use the following Excel 4.0 macro to automatically add an equal

sign to a selection of cells, except any blank cells or cells that contain formulas or text.

   A1: AddEqualSigns
   A2: =FOR.CELL("curcell")
   A3: =  IF(NOT(ISBLANK(curcell)))
   A4: =    IF(NOT(GET.CELL(48,curcell)))
   A5: =      IF(NOT(ISERROR(curcell+1)))
   A6: =         FORMULA("="&curcell,curcell)
   A7: =      END.IF()
   A8: =    END.IF()
   A9: =  END.IF()
   A10: =NEXT()
   A11: =RETURN()

Note that running this macro in your file causes your file to be slightly larger, because formulas take more space than numeric values.

Notes on Macro

   A4: Get.Cell(48) returns TRUE if cell contains formula
   A5: If there's an error, cell contains Text

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.

Additional query words: 5.00 wrong

Keywords          : xlmac kbfaq
Version           : MACINTOSH:5.0
Platform          : MACINTOSH
Issue type        : kbbug

Last Reviewed: June 1, 1999