+ Reply to Thread
Results 1 to 3 of 3

Excel calculation dependency of linked workbooks

  1. #1
    Registered User
    Join Date
    08-19-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Excel calculation dependency of linked workbooks

    Hi all,

    I'm new to this so please forgive me if I haven't named this thread correctly or if it is in the wrong place. OR if this questions has been answered before (I did try to google it).

    My question revolves around calculation dependency of linked workbooks, an example is probably the best way to ask my question:

    I have two workbooks WB1 and WB2. Automatic update of links is off and manual calculation is on.

    WB1 has a sheet with lots of data that changes from time to time.
    WB2 has the same sheet except the data is pulled from WB1 using direct links (e.g. =['WB1.xlsb]Sheet1'!A1), there may be thousands of these "direct" links.
    WB2 also has another sheet that does a bunch of heavy calculations based on the data that is being pulled through these direct links.

    If I open WB1 and then open WB2, the direct links want to update to make sure they are correct (and therefore the option to calculate will appear down the bottom left).

    My question is, once the direct links between WB1 and WB2 have been updated (calculated), if the values don't change (i.e the data was up-to-date and correct) will excel still go ahead and calculate the other sheet with all the heavy calculations? Or, does it realize that the data being pulled from WB1 hasn't changed and therefore it knows it doesn't need to calculate the formulas based on this data?

    I hope that all makes sense. If this question is answered elsewhere please feel free to post a link.

    Thanks in advance.

    lazz64

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,018

    Re: Excel calculation dependency of linked workbooks

    Hi and welcome to the forum

    To answer your question, im going to ask another 1. why are you pulling in the data from WB1 to WB2, instead of just running the calcs in WB1 straight against WB1...and alternatively, if you are pulling the data from WB1 into WB2, why even bother wit WB1, just enter straight into WB2?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    08-19-2012
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Excel calculation dependency of linked workbooks

    Hi there,

    This is a simplified example of what I am actually dealing with. I pull the data from WB1 to WB2 because having extremely calculation heavy formulas across workbooks causes WB2 to open extremely slowly.

    In answer to you second question, WB1 actually does more than just housing the data (i.e WB1 does it's own calculations on the raw data as well).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1