MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copied File Data Refer to Old File Address

  Asked By: Bogart    Date: Feb 26    Category: MS Office    Views: 10411

I really need your help for this problem.I create a Excel file, and
some cells data refer to the other spread sheet in the same
workbook. When I copy some page and save as a new file, the the new
file's cells' data refer to the orignal file's spreadsheet instead
of the new one.
Not sure if I make it clear, let me take an example:
I have a workbook named "A.xls", which contains Sheet1, Sheet2 and
In this workbook, Sheet1.Range("A1").formula="=Sheet2!$A$1"

However, when I save the Sheet1 and Sheet2 as new file
named "B.xls", In B.xls, the formula of Sheet1.Range("A1")becomes "=
[A.xls]Sheet2!$A$1". The cell in new file refer to old file address!
But I want to ger rid of "[A.xls]"!

Who can telll me on this problem?
Thanks very much !!



4 Answers Found

Answer #1    Answered By: Isabella Campbell     Answered On: Feb 26

The easy way would be to do a search and replace for [A.xls] with a blank in
the replace box.

Answer #2    Answered By: Logan Bouchard     Answered On: Feb 26

It depends on how you do it.

If you use move or copy sheet  to copy  Sheet1 and then to copy the Sheet2,
then this is certainly what you will get. When you copied  Sheet1, all Excel
could do was to put the reference to the old file. When you then copy
Sheet2, it doesn't alter this - nor can it be expected to.

If you copy Sheet2 first, then Sheet1, it has the same effect, although you
might expect it could get it right. I think the problem  is that B's Sheet2
is not clearly the same as A's Sheet2 by the time you come to copy Sheet1.

But, if you select both sheets and copy them in one go, it does indeed get
it right. It is a single copy operation, so there is no ambiguity.

Another way to do this, is to use save  As to save A.xls as B.xls, or simply
copy the file  in Windows Explorer. Then open B and delete Sheet3. This is
actually the way I normally do it, as there is nothing that can go wrong.

Failing all this, simply use Edit/Replace to remove the text string [A.xls]
from your formulas. Formulas are just strings, and can be edited with
Edit/Replace like anything else. If the string isn't being found, check the
options in the replace window and make sure that it's looking in formulas
not values.

Answer #3    Answered By: Aidyn Smith     Answered On: Feb 26

This is a Microsoft-unknown bug, which I, and a few of my ex-colleagues have
experienced recently. Happens when you open an Excel 2003 workbook  in 2007,
and then save  it as any other name, whether it be 2003 of 2007 format.

What triggers the bug exactly I don't know yet. Filesize greater than x?
Workbook contains internal and external links?

If you can explore, and give feedback, that would be great.

Many companies use old 2003 models, change data, and make important
financial decisions based on the "changed" results.

Answer #4    Answered By: Abbad Akhtar     Answered On: Feb 26

Another way is goto edit => link on that change source from a.xls to b.xls

Didn't find what you were looking for? Find more on Copied File Data Refer to Old File Address Or get search suggestion and latest updates.