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

Question

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: - however, this does not stop the windows from appearing.

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

Not exactly a complete answer but I would not focus on trying to find and replace the external spreadsheet references to update your formulas- Excel should be capable of maintaining external spreadsheet references when a filepath is changed if everything is set up correctly... finding and replacing the references is a work-around, and work-arounds 1) usually fall apart and 2) usually aren't necessary with proper planning.

So I would instead focus on the following: 1) What is happening to either of the spreadsheets that could be causing your spreadsheet to lose its references- is anything being done to the source spreadsheet between the references working and not working? If that file is being update either manually or via macro without proper care you could end up removing the references..

2) Examine the structure of what you are trying to do and how you are using these two spreadsheets- Do you need to have the data in the external spreadsheet? You could write a macro to copy and paste the data from the source file into a secondary tab on your main spreadsheet- that way all of your formulas would not include external references...

Hope this helps, TheSilkCode