One workbook copies links from another?
 

Hello all. I'm coming up against what seems a rather odd quirk in
Excel, and wondered if anyone could confirm it for me and, ideally,
help me work around it!

I'm working on a large 'output' workbook with a lot of links back to
another workbook. I'm saving backups of the file, and regularly need to
refer to the backups to check what's changed. Opening the 'old' version
with nothing else open gives, as you'd expect, the old numbers. Opening
the 'old' version with the database it links back to gives it the new
numbers, again as you'd expect. However, the problem is that opening
the old and new workbooks together, *without* opening the workbook both
refer to, still updates the 'old' workbook with new numbers! It seems
that it copies the more up-to-date numbers from the new workbook, even
though the master workbook that they're both linked to is closed.

Firstly, is my guess at what's happening correct? And secondly, is
there any way to compare the two workbooks without having to print one
out, close it, open the other one and do it all manually? I also don't
want to value-paste all the different sheets in the old workbook, for
two reasons: I'd like a record of what the formulas should be, and
there is a large number of sheets!

I've looked around the web, and have only found
http://groups.google.com/group/micro...19e7de093a4b4/
which could be a similar issue. Most file-comparison macros seem to
need both worksheets open at once, which I can't do. Any advice?

Thanks in advance,

Ben

Sorry, forgot to mention. I'm using Excel 2000 on Windows 2000.

Thanks,

Ben

Hi Ben

Firstly, this is how Excel works and I don't believe there is any way
to change it. In some circumstances it could be viewed as a virtue,
that the open workbooks do not have inconsistent information from the
same source. In your case it is not a virtue.

I guess you could backup the source file as well as the "output" file,
when both are open - so that the links in the output file refer to the
source-backup and not the current source.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill,

Thanks a lot for the reply: it's nice to confirm what seemed to be
happening! And yes, I can certainly see the sense in the way it's set
up at the moment: it just makes this particular job a bit harder. I'm
afraid backing up the source file isn't really an option - a large
number of spreadsheets feed off it - so I'll carry on with what I have
been doing in the past. Thanks for your help!

Ben