I have two Excel files open. One contains a table of data. The columns represent months; the rows represent sales and expense figures for various regions and business units.
The other file contains formulas which link to the data in the first file. The formulas include VLOOKUP or HLOOKUP. For example, I might have a VLOOKUP intended to return the sales figure for a specific month in a specific region.
When both files are open, everything works fine; the formulas always return the values that they should.
However, at times (and the occurrences seem random; I cannot find a pattern), the following problem happens:
a. With both files open, I recalculate all formulas.
b. I then save and close both files.
c. At a later time, I re-open the file with the lookup formulas and do not refresh links (takes much too long; source file is large)
d. Instead of returning the correct values, the lookup formulas sometimes return either #REF! or simply a value of 0 when there should be some other number there.
e. As noted, this seems to happen at random; it does not happen every time I open the file.
f. Even more randomly, it does not seem to affect ALL the formulas. For example, I might have a column of identical formulas that vary only according to which region they are looking up the data for; sometimes, some of the formulas in the column work and some don't.
g. If I open the source file and recalculate, the problem goes away; the formulas then return the correct values.
I am (still) using Excel 2003.
I am totally baffled by this. Any suggestions much appreciated.
Thank you.
The other file contains formulas which link to the data in the first file. The formulas include VLOOKUP or HLOOKUP. For example, I might have a VLOOKUP intended to return the sales figure for a specific month in a specific region.
When both files are open, everything works fine; the formulas always return the values that they should.
However, at times (and the occurrences seem random; I cannot find a pattern), the following problem happens:
a. With both files open, I recalculate all formulas.
b. I then save and close both files.
c. At a later time, I re-open the file with the lookup formulas and do not refresh links (takes much too long; source file is large)
d. Instead of returning the correct values, the lookup formulas sometimes return either #REF! or simply a value of 0 when there should be some other number there.
e. As noted, this seems to happen at random; it does not happen every time I open the file.
f. Even more randomly, it does not seem to affect ALL the formulas. For example, I might have a column of identical formulas that vary only according to which region they are looking up the data for; sometimes, some of the formulas in the column work and some don't.
g. If I open the source file and recalculate, the problem goes away; the formulas then return the correct values.
I am (still) using Excel 2003.
I am totally baffled by this. Any suggestions much appreciated.
Thank you.
