Total A.xlsx
contains formulas adding cells (without the help of functions, just the "+" sign) from several files named 01 A.xlsx
, 02 A.xlsx
and so on.
All files (including the total) are in the same folder on a server mapped as M:
I don't see any error in cells (no colored corners in cells, no #REF!
or #VALUE!
, totals are as expected), in Edit Links updating all is OK, but when trying to evaluate any formula, the steps are like this:
#REF!+rest
of the formula#REF!+#REF!+rest
of theformula=#REF!+rest
of the formula
Step 2 repeats until the end.
Last step: #REF=number
(the value I see in the cell and it is perfect)
It's like when you enter a password and you are pressing different keys, but the field displays ****
and when you send the data, it works despite seeing ****
.
I have recently switched from Excel 2003 to Excel 2007. I've converted all the files to xlsx and updated the links by replacing xls with xlsx.
Everything looks fine, but evaluating formula is always #REF!
until the last step.
I was using evaluation formula to quickly find which file contained a certain value. It was pretty seldom and random enough to not require a permanent breaking of the formula in separate cells.