Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Links / October 2005

Tip: Looking for answers? Try searching our database.

Links picking up values from an older version of linked file

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Cate - 18 Oct 2005 17:22 GMT
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.  Let’s 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
Bill Manville - 18 Oct 2005 23:02 GMT
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
Cate - 19 Oct 2005 14:10 GMT
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
Bill Manville - 20 Oct 2005 00:33 GMT
> 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
Cate - 20 Oct 2005 13:53 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.