Excel User Group
Microsoft Excel blogs, forums, files. Read, ask questions, provide answers.
Forums » Excel Forums » Excel 2007 and 2010 » How to update destination file without opening base file?

How to update destination file without opening base file?

Previous | Next
Latest post Thu, May 13 2010 3:07 PM by bentleybob. 1 replies.

  • Tue, Apr 20 2010 3:50 AM

    • suhrudlele
    • Not Ranked
    • Joined on Tue, Apr 20 2010
    • Posts 1
    • Points 21

    How to update destination file without opening base file?

    Reply |Contact

    Hi All,

    This might be a repeated query so plz excuse me but in-spite of searching the forums I couldn't get solution to my problem.

    Please help me with my query regarding external links.

    I have a destination excel file (viz FileNo1). This file contains cells having references to other excel files (FileNo2 & FileNo3)

    To update the destination file, I have to open all base files simultaneously, which is quite irritating.

    Every time I update/change values in any of the base files (No2 & No3) I have to open the destination file to update changes to it. At the same time, I have to ensure that both base and destination files are open. 

    Now my query is that-
    1) how can I update the destination file without opening the base files everytime? 
    2) If I change values in base file, can these be updated in destination file without opening the destination file? 

    So does this query has a solution or is this a limitation of Excel 2007?

    Thanks in advance.

    • Post Points: 21

  • Thu, May 13 2010 3:07 PM In reply to

    • bentleybob
    • Top 100 Contributor
    • Joined on Fri, Feb 27 2009
    • Posts 9
    • Points 77

    Re: How to update destination file without opening base file?

    Reply |Contact |Mark as Not Answer

    In my experience, the answer to #1 depends on what types of file links you are using.  Direct cell-to-cell references are easy:  use Data Edit Links and then hit Update Values for any files linked in this manner.  Other types of links, such as SUMIFs, don't work that way and give you #VALUE messages.  My work-around there is to have an autorun macro that opens each file and then closes it (you don't have to have ALL of these files open simultaneously).  That way, it's automatic when I open the destination file.

     

    I believe the answer to #2 is No, the destination file has to be open.
    • Post Points: 5
Page 1 of 1 (2 items) | RSS
Copyright Excel User Group and the relevant contributors, 2010. All Trade Marks acknowledged. This site is a peer-to-peer site and NOT affiliated in any way to Microsoft Corp. All rights reserved.