3

I am currently working on an MS Excel 2013 spreadsheet (p:\master.xlsx) where some cells contain values that are directly linked from other MS Excel 2013 spreadsheets (p:\path1\feeder1.xlsx, p:\path1\feeder2.xlsx, etc).

What I am finding occasionally (not every time), is when I open up my p:\master.xlsx spreadsheet (and then "enable editing" and "enable content"), some of the values in this spreadsheet change from the correct linked value (a number) to value #REF.

When I look at the formula within these offending cells, I see it has also changed from say: 

=MAX('P:\path1\[feeder1.xlsx]Sheet1'!$C:$C)

to

=MAX('P:\path1\[feeder1.xlsx]#REF'!$C:$C)

I can confirm the feeder.xlsx spreadsheet has not been moved or renamed and has not had it's values changed at all. The network drive the files sit on is also stable.

What is confusing me is that this appears to happen at random times (as opposed to every time). In addition, not all the cells revert to #REF - some of the values are still OK (and thus the formula is OK).

Due to not knowing if or when the formulas will change to their "#REF" status, I need to save the file every time I make a change (slightly frustrating).

I searched the forum and noticed another user had a similar issue on MS Excel 2010 but the answers provided did not appear to solve the user's issue. Anyone have any suggestions?

Ibraheem Zafar
  • 346
  • 1
  • 9
Courtenay L
  • 55
  • 8
  • First off, saving a file every time is a good habit to get into, but obviously I understand that this is annoying. Could you confirm whether it's every cell that errors out (when it does happen)? Or every cell that references feeder1.xlsx? Or every cell that references sheet1 on feeder1.xlsx? – Grade 'Eh' Bacon Jul 31 '15 at 12:18
  • hi there, I can confirm that it happens for every cell from ONE feeder file,but NOT ALL feeder files. The master.xlsx spreadsheet takes 4 inputs from each of the feeder files. There are 50 feeder files (feeder1.xlsx, feeder2.xlsx, feeder3.xls.....feeder50.xlsx) - thus there are 200 inputs. What I am finding is that when the #REF error occurs, if one of the inputs from feeder1.xlsx fails, all 4 of the inputs from feeder1.xlxs fail. However the inputs from other feeders files are OK. At other times, the inputs from, say, feeder30.xlsx will fail, however all other inputs are OK. – Courtenay L Aug 01 '15 at 10:12
  • Just a shot in the dark here, but is there any chance either a person or a macro changing the name of the sheet? For example, a macro might run and create a new sheet with all the info daily, then delete sheet1 and rename the new sheet as Sheet1. This could cause a REF error due to there being a point where Sheet1 does not exist. – Fred Dec 22 '16 at 17:28
  • Hey Fred - I can confirm no other person was using the file and no macros were being run which changed the names of the sheet. Interestingly enough what I just discovered was the cells that changed to value #REF was when both the master file was open at the same time as the feeder file. So if feeder1.xlsx was open at the same time as master.xlsx, all values in the master.xlsx that related to feeder1.xlsx changed their values to xlsx. – Courtenay L Dec 24 '16 at 08:52

1 Answers1

0

First make sure you're formula is not too long if your using 97-2003. if so save the file as .xlsx

if that don't work do the following

  • close all open workbooks.
  • open the workbook with the (now) broken links (#ref).
  • Click on the Data tab
  • Click on Edit Links.

Excel doesn't like links to closed workbooks

James Chen
  • 237
  • 2
  • 6
  • 18
  • Hey James - I'm actually using MS Excel 2013. The formula I am using is also quite short. In my experience MS Excel 2013 has not had any issues with linking to close workbooks. – Courtenay L Aug 20 '15 at 02:26