+ Reply to Thread
Results 1 to 6 of 6

How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

  1. #1
    Registered User
    Join Date
    05-07-2008
    Location
    London, UK
    Posts
    9

    Question How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Hello,

    I'll try to be brief ;-P

    All my excel files in the office are in a network drive (mapped on my OS) and I have one BIG file that has thousands of formulas that retrieve data from external Excel files within a precise and self-contained folder structure. Every day the whole folder structure is backed up on my laptop's internal drive so that I can sometimes work on my Excel files from home.

    When I am home, I re-open the BIG file, and Excel automatically changes all the paths of the files from which it gets the values within the formulas using the local path.

    EXAMPLE:
    from
    =SUM('Z:\R - REPORTS FROM THE FIELD\02 ABC\[2012 ABC Weekly Report Template-3.xlsx]Sheet1'!$F$67:$G$69)+SUM('Z:\R - REPORTS FROM THE FIELD\02 ABC\[2012 ABC Weekly Report Template-3.xlsx]Sheet1'!$F$23:$G$23)
    to
    =SUM('E:\Backup\R - REPORTS FROM THE FIELD\02 ABC\[2012 ABC Weekly Report Template-3.xlsx]Sheet1'!$F$67:$G$69)+SUM('E:\Backup\R - REPORTS FROM THE FIELD\02 ABC\[2012 ABC Weekly Report Template-3.xlsx]Sheet1'!$F$23:$G$23)

    Sometimes Excel changes the initial part of the path within all formulas (the best scenario), sometimes it does not do it for all of them, but only for some of them, with the result that not all formulas are recalculated successfully.

    Today, for example, I need to change a lot of formulas from home, but I want to work on the original formulas (keeping the Z:\ path of the network drive in the office).
    1- I want to open my file from my laptop's drive and see all the formulas pointing to Z:\ (I'd like to have the path frozen - cell values frozen)
    2- I want to change my formulas keeping the Z:\ path in them, but avoid that Excel automatically computes them every-time something changes (Excel stores the new formulas without trying to compute them to get the new values until I order it to do so manually)
    3- I want to re-open the file I worked on from home back in the office and tell Excel to compute only then (the network drive properly mapped on my laptop) all the formulas and get the new cell values.

    How can I do that? Is it possible with Excel 2007? If so, how?

    Thanks in advance for your help, this is a pivotal issue for me in the future months when I will have to work far from the office every other day!

    H.

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Hi Hertz,

    To avoid calculation to happen at the time of workbook opening, you can switch the calculation to manual and uncheck the option "Recalculate workbook before closing".

    And after this to calculate only one sheet (in which u made changes), press Shift + F9. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey, Excel rMVP
    +919810929744 (India), +971528225509 (Dubai), [email protected]

  3. #3
    Registered User
    Join Date
    05-07-2008
    Location
    London, UK
    Posts
    9

    Re: How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Thanks for the reply!

    I tried opening Excel 2007, and turned off the automatic calculation as well as the option "Recalculate workbook before closing". First of all the options are available only when at least the blank workbook is open. I then opened my file, my all the paths were automatically changed to the local drive and lost the network path at work (Z:\....).

    I do not know why, but it did not work for what I need.

    Any other option?

    H.

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Okay..

    For network linking.. its always a good practice to have all the links starting from server name / ip address.. for example, you can map a LAN drive in Z and your colleague can map that as J

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    05-07-2008
    Location
    London, UK
    Posts
    9

    Re: How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Thanks, that is something I will do from now on!...very good idea.

    Is there a way to let Excel do (or not do) what I need? Or should I be hopeless?

    H.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: How to force Excel2007 NOT TO calculate formulas/links when opening/editing remotely

    Hi Hertz1976,

    For now, you can do "find and replace" in all the formulas having link to server / LAN paths
    and may be then try again .. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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