title: Excel formula returns #REF when linking to external worksheets and copied
body: <p>I have a spreadsheet October2016.xls with formulas for external links to other sheets. When I copy the sheets to a different directory, I will get a #REF error. For example, I need to link to Assembly Totals.xls</p>
  
  <p>On the original directory on my computer, I will open the October2016.xls file and in a cell I will see something like this:
  ='C:\users\desktop...PROD DEPT\PAPERLESS\2016\Oct\Defects[Assembly Totals.xls]!Total'!A1</p>
  
  <p>When copied to a different computer (or file location on my computer) I will get this:
  ='C:\users\desktop...PROD DEPT\PAPERLESS\2016\Oct\Defects[Assembly Totals.xls]!#REF'!A1</p>
  
  <p>I am aware that the formula is still trying to follow the old path. But obviously when copied to a different directory it will get confused. If I replace the #REF with the word 'Total' then it works fine - I assume this is where relative referencing comes in handy. The problem however is that Excel wants me to choose the linked spreadsheet in the formula (Assembly Totals.xls) from an Update Values window to get to the newly saved path/file location. This is tedious when you have over 100 replacements.</p>
  
  <p>I have tried writing a Find and Replace Macro to alter the path. i.e. Replace the word #REF with the word Total. I also replace a part of the old path with a "blank" since that part of the path is no longer valid in the new directory path. For Example, assume I only copied the files from \Defects[Assembly Totals.xls]!Total'!A1. Thus I will be removing "PROD DEPT\PAPERLESS\2016\Oct\" because the new computer will not have those files.</p>
  
  <p>However I still get hundreds of update value windows where I need to select the file.</p>
  
  <p>Here is what I have done to eliminate the update values window: - however, this does not stop the windows from appearing.</p>
  
  <pre><code>Sub terfuge()
  ' todo: Assign keystroke shortcut
  If Application.DisplayAlerts Then
  Application.DisplayAlerts = False
  Else
  Application.DisplayAlerts = True
  End If
  End Sub
  </code></pre>
  
  <p>Here is my find and replace function - which works fine - it's just the Update values window that keeps appearing</p>
  
  <pre><code>Sub Multi_FindReplace()
  
  Dim sht As Worksheet
  Dim fndList As Variant
  Dim rplcList As Variant
  Dim x As Long
  
  
  
  fndList = Array("#REF", "PROD DEPT\PAPERLESS\2016\Oct\")
  
  rplcList = Array("Total", "")
  
  'Loop through each item in Array lists
  For x = LBound(fndList) To UBound(fndList)
  'Loop through each worksheet in ActiveWorkbook
  For Each sht In ActiveWorkbook.Worksheets
  sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
  LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
  SearchFormat:=False, ReplaceFormat:=False
  Next sht
  
  Next x
  
  End Sub 
  </code></pre>
  
  <p>Thanks
  Martin</p>