Assuming Excel is not corrupt, this response occurs because Excel cannot find the worksheet named in the formula in the current workbook. (OK I know thats pretty obvious - but bear with me)
I have written a short macro which will test each cell in a selected range to see if the sheet name in each formula exactly matches one of the worksheets in your workbook.
In the VBA window (Alt+f11) insert a new module (not a class module).
Enter this code:
Dim n As Integer
Dim m As Integer
Dim strSrcArry() As String
Dim rngCell As Range
Dim wsEach As Worksheet
Dim blnMatch As Boolean
'get number of selected cells
n = Selection.Cells.Count
'resize array to match
ReDim strSrcArry(n, 1)
n = 0
For Each rngCell In Selection
'put sheet names of cell references into array
'flag which array members have a formula
If rngCell.HasFormula = True Then
strSrcArry(n, 0) = "F"
strSrcArry(n, 1) = Mid(rngCell.Formula, 2, InStr(2, rngCell.Formula, "!") - 2)
n = n + 1
strSrcArry(n, 0) = "NF"
n = n + 1
For Each wsEach In ActiveWorkbook.Worksheets
blnMatch = False
For n = 0 To UBound(strSrcArry, 1)
If strSrcArry(n, 0) = "F" Then
If strSrcArry(n, 1) = wsEach.Name Then
blnMatch = True
MsgBox strSrcArry(n, 1) & " matches this Tab: " & wsEach.Name
If blnMatch = True Then Exit For
If blnMatch = False Then
MsgBox "No Match for Tab: " & wsEach.Name
End SubGo back to your worksheet with the formulas and select a group of cells containing the linking formulas (=Sheet2!A1 etc)
Now run the Macro (back in the VBA window, click anywhere inside the subroutine and hit f5)
The macro will either give you a series of matching messages or one or more No match messages.
If you get matching messages, you will know that there are no subtle, non-visible differences between your formula sheet names and the names on the Tabs.
If all the formulas result in Matches then it is likely that Excel is corrupt and a repair is called for.
If you get No match messages, then a more detailed look at the formulas and worksheet names is called for.
Hope this helps