Microsoft KB Archive/46483

Mac Works: Circular Reference Found in Spreadsheet Recalculation

PSS ID Number: Q46483 Article last modified on 10-22-1998

1.0 1.1 2.0 3.0

MACINTOSH

=
========================================================= 1.00 1.10 2.00 3.00 MACINTOSH kbother

= SUMMARY =

When you include formulas in a table referenced by an HLookup or VLookup function in the Microsoft Works Spreadsheet module, and you attempt to insert additional rows or columns, you may get the following error message:

Circular reference(s) found during Spreadsheet recalculation

For example, a table referred to by an HLookup function might contain a row of numbers and a corresponding row of formulas that take a number in the first row and multiply it by a constant. Inserting a row or a column at any location on this spreadsheet may bring up the circular reference message. This message appears every other time the Insert command is used.

To avoid getting this error message, do one of the following:

Workaround 1
Use the Lookup function.

Workaround 2
Freeze the values in the table referred to by HLookup or VLookup as follows:


 * 1) Select the cells that contain formulas.
 * 2) From the Edit menu, choose Copy.
 * 3) From the Edit menu, choose Paste With Options.
 * 4) In the Paste With Options dialog box, select Values, then choose the OK button.

This behavior was corrected in Works for the Macintosh version 4.0. It allows the insertion of rows and columns in VLookup and HLookup tables, but it is necessary to manually update formulas that use these functions to reflect the proper offset for the function. The offsets are not automatically updated when rows or columns are inserted.

KBCategory: kbother KBSubcategory: macworkskb

= Additional reference words: 1.00 1.10 2.00 3.00 mwksprb mwksss =

Version : 1.0 1.1 2.0 3.0 Platform : MACINTOSH Issue type : kbinfo ============================================================================= Copyright Microsoft Corporation 1998.