0

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:

  1. #REF!+rest of the formula
  2. #REF!+#REF!+rest of the formula=#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.

6
  • recheck the external cell references and make sure you entered the full path of the references ='FilePath[WorkbookName.xlsx]SheetName'!CellReference (With absolute value)
    – iSR5
    Apr 17, 2016 at 22:39
  • @iSR5 Are you implying that the number I see in the cell is an old value saved at some point in time?
    – Anna
    Apr 17, 2016 at 22:47
  • yes, it's one possibility, since Excel saves the values that linked from other workbooks, and when you switched to 2007, these linked were broke or already broke before switching, so it's a good thing to recheck the referencing links first to know what further actions you need to take.
    – iSR5
    Apr 17, 2016 at 22:51
  • I've tried with new files and just one cell used in each of them, but the problem is still there. The formula is ='M:\test[01 A.xlsx]Sheet1'!$A$1+'M:\test[02 A.xlsx]Sheet1'!$A$1 I've noticed that evaluating to #REF! in "evaluate formula" happens only when the linked files are closed.
    – Anna
    Apr 18, 2016 at 7:33
  • You have to put \ after the folder name, so the formula should be : ='M:\test[01 A.xlsx]Sheet1'!$A$1+'M:\test[02 A.xlsx]Sheet1'!$A$1
    – iSR5
    Apr 18, 2016 at 13:09

0

You must log in to answer this question.

Browse other questions tagged .