The following macro will check every sheet in the active workbook for
formulas with external references. It will list them all on a new sheet.

Sub FindExtRef()
'This macro should find any formulas with external references, and list them
on a new sheet.
'Declare local variables.
Dim x As Long, c As Range, y As Long, z As Long
Dim NuSht As Worksheet, HitCount As Long, Msg7 As String
On Error Resume Next
Application.Cursor = xlWait
'Add a new worksheet to the current workbook at the end.
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Set NuSht = ActiveSheet
HitCount& = 1
'Check every sheet in turn.
DoEvents
For x = 1 To Worksheets.Count
'Activate each sheet in turn.
Sheets(x).Activate
'If this sheet has any formulas, then select all cells with formulas in them.
If HasRx(ActiveSheet) = True Then
ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select
'Check every cell in the selected range.
For Each c In Selection
'Check every character in the formula.
For y = 1 To Len(c.Formula)
'First look for a [ character.
If Mid(c.Formula, y, 1) = "[" Then
'If [ was found, check the rest of the formula for a ! character
For z = y + 1 To Len(c.Formula)
If Mid(c.Formula, z, 1) = "!" Then
'Found an external reference! Store the cell's sheet name, address, and
formula on NuSht.
HitCount& = HitCount& + 1
NuSht.Cells(HitCount&, 1).Value = "'" &
ActiveSheet.Name
NuSht.Cells(HitCount&, 2).Value = "'" &
c.Address
NuSht.Cells(HitCount&, 3).Value = "'" &
c.Formula
Exit For
End If
Next z
End If
Next y
Next c
End If
Next x
'Done. Clean up. Add headings for the output rows and resize all columns on
NuSht.
If HitCount& = 1 Then
MsgBox "No external references were found", vbInformation,
"FindExtRef macro"
Application.DisplayAlerts = False
NuSht.Delete
Application.DisplayAlerts = True
GoTo FER_Cleanup
End If
NuSht.Cells(1, 1).Value = "Sheet"
NuSht.Cells(1, 2).Value = "Cell"
NuSht.Cells(1, 3).Value = "Formula"
NuSht.Cells.Select
NuSht.Cells.EntireColumn.AutoFit
Calculate
NuSht.Activate
FER_Cleanup:
'Free object variables.
Set NuSht = Nothing
Set c = Nothing
'Restore the cursor.
Application.Cursor = xlDefault
MsgBox "Done!"
End Sub

When you copy & paste this into a VBA module, watch for any lines that may
have wrapped. They could generate an error in the VB Editor.

Hope this helps,

Hutch

"Ryan" wrote:

I am working with a huge workbook that contains many large worksheets. In
these work sheets, there are several dead links to other workbooks, and when
I open this particular book, it prompts me to locate the others. The other
books have either been deleted or can not be located, so I am trying to
search this workbook for the cells that contain the equations with the links
in them so I can delete them. I have tried using Ctrl+F, but was
unsuccessful. If there is an easier way to search multiple sheets within a
book (some are hidden, as are several cells), could some one please let me
know. Thanks Alot!!