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.
Bookmarks