Links/cross-references between sheets

[bingbest]

Does anyone know how to determine if there is any cell in a sheet that links (feeds into) another sheet within the same workbook? i.e. I want to delete Sheet1, but don't know if any other sheet in the workbook is linked to any cell in Sheet1. I know I could do it manually (save workbook - delete Sheet1 - check to see if there are any REF errors anywhere else), but want to know if there is a built-in function.

If there is no built in function, does anyone have a macro that does this? Essentially, the macro would go through every cell in the sheet and check trace dependents.

 

[JoeMo]

With Sheet1 active, press the F5 function key and you will see the GoTo dialog box. Press the Special button at the bottom of this box and select Formulas. Then on the Formulas menu choose Trace Dependents. If you see any spreadsheet icons, double-click the arrow between the cell and the icon to see which sheet(s) depend on Sheet1.

 

[bingbest]

Thanks for the reply, but if I go F5-Special-Formulas there is no way to also click Trace Dependents because the Formulas and Trace Dependents options are mutually exclusive buttons - i.e. you can't choose them both at the same time.

The Formulas option just highlights all the cells that have formulas in the sheet.
The trace dependents works only if you highlight the whole sheet before, but also shows all the trace dependents within the sheet, when I'm looking to see if there are dependents from Sheet1 to outside of Sheet1 (not within Sheet1).

 

[JoeMo]

Try this (change sheet name to suit):

Sub DependsOnSheet()
Dim sSht As Worksheet, sht As Worksheet, sNam As String, rng As Range, msg As String
sNam = "Sheet1"  ' change sheet name to suit
Set sSht = ActiveWorkbook.Sheets(sNam)
For Each sht In ActiveWorkbook.Sheets
    On Error Resume Next
    Set rng = sht.UsedRange.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not rng Is Nothing Then
        If Not rng.Find(sNam, rng.Cells(1, 1), , xlPart) Is Nothing Then
            MsgBox "Sheet " & sht.Name & " has at least one dependency on sheet " & sNam
        End If
    End If
Next sht
        
End Sub