Excel formula returns #REF when linking to external worksheets and copied to other directories

Question

Hi

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

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

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

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.

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.

However I still get hundreds of update value windows where I need to select the file.

Here is what I have done to eliminate the update values window found on this thread.

However, this code does not stop the windows from appearing when I run the find and replace the first time around.

Sub terfuge()
' todo: Assign keystroke shortcut
If Application.DisplayAlerts Then
Application.DisplayAlerts = False
Else
Application.DisplayAlerts = True
End If
End Sub

Here is my find and replace function - which works fine - it's just the Update values window that keeps appearing

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 

Thanks Martin

Answer 1

Hi Martin,

To better address your concern, please post your query to this thread.

Regards.