This is my first post, so bear with me.

Can some one confirm if this is the same issue as Discussion €œOne workbook
copies links from another?€� started on 9/13/2005?

I am hoping this will explain my problem clearly enough that some one will
be able to tell me why this happens. I am using Excel 2002.

I have a file (call it File YTD) that contains year to date data, which is
updated every month; plan values are update to actual values. See simple
illustration of Q3 subtotal area:

When actual data was entered for August:
JUL (ACTUAL) 123
AUG (ACTUAL) 456
SEP (PLAN) 300
Q3 (PLAN) 879

When actual data was entered for September:
JUL (ACTUAL) 123
AUG (ACTUAL) 456
SEP (ACTUAL) 320
Q3 (ACTUAL) 899


Then I have monthly files that link to File YTD. Lets call the monthly
files File Aug and File Sept. Both link to File YTD. I have File Sept open
and linked to the current data in File YTD €“ for SEP the ACTUAL amount would
be 320. (Note: File YTD is not open.) While File Sept is still open, I open
File Aug and when asked if I want to update links I answer no. When I go
back to File Sept the amount for SEP changes to 300, the PLAN amount for SEP
from File YTD as it appeared when it was updated in August. Why does this
happen? I €œfixed€� it by updating the link to File YTD in File Aug. Even
though I figured out what happened and how to fix it, can anyone tell me why
File Sept would update its link to the August version of File YTD?

Thanks,
Cate

Excel knows that one cell cannot have 2 different values.
So if you have two files open both with links to the same cell in a
closed file it strives to make the two values the same.

Since you didn't allow File Aug to go and read File YTD when you opened
File Aug, Excel decided to take the old value for the linked cell from
File Aug.

It may not be what you want, but it is what Excel has alweays done.

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

Bill,

Thank you for your response.

I think you have misunderstood me a little bit. File Aug and File Sept do
not link to the same cell in File YTD. File Aug would link to the cell that
contains 456 and File Sept should link to the cell that contains 320 as saved
for September. The File YTD was updated with the actual amount of 320 and
closed. When I linked File Sept to that cell it was correct until I opened
File Aug, remember the link for File Aug is a different cell. As I said
before when I opened File Aug the link for File Sept reverted back to the
August version of the File YTD - 300. Also as I said I figured out how to
"fix" it, but I still don't understand why the File Sept link would revert
back to the amount from when the File YTD was saved in the prior month. It
just seems really odd that File Sept's link went back to a number on a
version of File YTD does not even exist anymore.

If File Aug and File Sept did link to the same cell, I would think they
would both update to the current version of File YTD.

Regardless, I suppose I will just need to remember when I open two months of
File Mth to compare something I will need to update the links on the older
one.

Sorry to go on and on, but it is frustrating to know a "fix" to something
that I wish I knew the proper way to do it and understand why. I am
continually checking and double guessing my numbers rather than saving the
time by linking files, it just creates more work with all the checking. Or
is to update the links in the older file the "proper" way to do it?

Thanks again for your response,
Cate

Cate wrote:
Or
is to update the links in the older file the "proper" way to do it?


Sorry I misunderstood you.
I think you have the right solution.

In general I would only recommend not updating links when you know you
have up to date information (or you don't care!) - and when updating
takes a long time becaues of the number and nature of the formulas.

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

Bill,

Thanks for your correspondence. I still can't say that I understand why it
happened the way it did, but at least now I know what I need to do in those
situations.

Cate