August 12, 2010

When moving worksheets or ranges of cells containing formulae between workbooks, the formulae that reference other worksheets will retain their links to the original workbook. In some cases this might be desirable but when it is not intended there are at least 2 ways to avoid this link to the original workbook.

  1. Before moving the worksheet/range, ‘find and replace’,  all ‘=’ signs with a symbol that may be reversed back to the ‘=’ sign in the destination workbook. As an example, ‘find and replace’ all ‘=’ with ‘#’, move the worksheet/range and then reverse this by replacing all ‘#’ with ‘=’ in the destination workook.
  2. Move the worksheet/range as it is and in the destination sheet select edit -> links -> change source and then browse to the location of the destination file and select it. This will change the formulae to look at the objects within the destination workbook.

Craig Juta, Sage Accpac Intelligence consultant


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: