Finding References to Nonexistent Cells

As one can see from this article and J Woolley's responses and macros, merging cells can be problematic. Determining the consequences of merged cells is difficult. Some advice I received very early on in my Excel journey was, simply, "Don't Merge Cells".

If you google "should you merge cells in Excel", you will get the following (AI summary) response.
"Generally, it's best to avoid merging cells in Excel unless absolutely necessary. Merging cells can create problems when sorting, copying, pasting, or moving data, especially in more complex spreadsheets. However, for simple formatting and centering, consider using the "Center Across Selection" feature as an alternative."

As you can see, it is not just formulas that can be affected, but things like sorting, filtering, copy and pasting, etc. Over the years, I've seen opinions where people consider the merging cells feature as ill-advised and poorly thought through before introduction. As a note, the Merge and Center feature was a part of Excel ver 1.0, released 40 years ago in 1985. I don't know what history this feature has in other early spreadsheet products such as Lotus 123 (which was my first spreadsheet) or VisiCalc (never used). Personally, in over 30 years of using Excel, I don't think I've ever found myself thinking, "Darn, I really want to merge cells here, but know I shouldn't".

About the only use case I can think of where merging cells would be ok is if one is designing a form print out for paper (by hand) use only. Purely for 'looks'. No formulas, or links, or input, etc. A scenario that is probably a rarity today.

My most recent comment below said the ZombieDependents macro does not resolve the following pair of issues:
1. If MyName is a named range that references a zombie cell, the macro does not identify a formula that references MyName.
2. If D3 is a zombie cell on Sheet1 in Book1.xlsx, the macro does not identify a formula in another workbook that references [Book1.xlsx]Sheet1!D3.
Actually the macro does resolve the 1st issue, and the 2nd issue is addressed by substituting the following for Function SheetAddress:

Function SheetAddress(Target As Range) As String
    'Return Target's external range address
    SheetAddress = Target.Address(External:=True)
End Function

In this case, you can also remove the # symbol (only) from both HYPERLINK formulas that begin like this:
    rCell(...).Formula = "=HYPERLINK(""#" ...
Regarding formulas in another workbook (i.e., the 2nd issue), that workbook must be open but inactive before using the macro to determine the active workbook's zombie dependents.

When you merge a contiguous range of cells, the merged result is given by the range's top-left cell. So if C3:E4 are merged, the merged result is C3. Other cells in the merged range lose their contents and cannot be directly referenced; they become dead. You might call these dead cells zombies because they will live again if the merged cell is unmerged, but they will be empty.
The following macro traces zombie dependent formulas directly using methods described in https://excelribbon.tips.net/T008271 and in https://stackoverflow.com/a/72678656/10172433

Sub ZombieDependents()
    'Report formulas that refer to a zombie cell in a merged cell
    Const myName As String = "ZombieDependents"
    Dim Zombies As New Collection, Dependents As New Collection
    Dim rSheet As Range, rFound As Range, rArea As Range, rCell As Range
    Dim sFirst As String, sAddr As String, msg As String
    Dim nArrow As Integer, nLink As Integer, n As Integer, k As Integer
    Application.FindFormat.Clear
    Application.FindFormat.MergeCells = True 'find merged cells only
    For n = 1 To Worksheets.Count 'each worksheet in active workbook
        Set rSheet = Worksheets(n).Cells
        Set rFound = rSheet.Find(What:="", After:=rSheet(1), _
            SearchDirection:=xlNext, SearchFormat:=True)
        If Not rFound Is Nothing Then
            sFirst = rFound.Address 'first merged cell
            Do
                Set rArea = rFound.MergeArea 'range of merged cell
                rFound.UnMerge
                For Each rCell In rArea 'cells originally merged
                    If rCell.Address <> rFound.Address Then 'zombie cell
                        sAddr = SheetAddress(rCell)
                        rCell.ShowDependents 'locate dependent formulas
                        On Error Resume Next
                            nArrow = 1 'local/external dependent trace arrow
                            Do
                                nLink = 1 'follow external dependents first
                                Do
                                    rCell.NavigateArrow False, nArrow, nLink
                                    If SheetAddress(ActiveCell) = sAddr Then Exit Do
                                    Zombies.Add sAddr 'zombie's address
                                    Dependents.Add Selection 'might be multi-cell
                                    nLink = nLink + 1 'find next dependent
                                Loop
                                If nLink = 1 Then Exit Do 'no more dependents
                                nArrow = nArrow + 1 'next dependent trace arrow
                            Loop
                        On Error GoTo 0
                        rCell.ShowDependents Remove:=True 'remove trace arrows
                    End If
                Next rCell
                rArea.Merge 'restore original merged cell
                Set rFound = rSheet.Find(What:="", After:=rFound, _
                    SearchDirection:=xlNext, SearchFormat:=True)
            Loop Until rFound.Address = sFirst 'next merged cell
        End If
    Next n 'next worksheet
    Application.FindFormat.Clear
    k = Dependents.Count
    If k < 1 Then
        MsgBox "There are no zombie dependent formulas", myName
        Exit Sub
    Else
        sAddr = "$A$" & (ActiveSheet.UsedRange.Rows.Count + 1)
        Range(sAddr).Select
        Set rCell = Nothing
        msg = "There are " & k & " zombie dependent formulas. " _
            & "The results will require 3 columns and " & (k + 1) _
            & " rows. Select the top-left corner of a " & (k + 1) _
            & "x3 range of empty cells on any sheet for the results."
        On Error Resume Next
            Set rCell = Application.InputBox(msg, myName, sAddr, Type:=8)
        On Error GoTo 0
        If rCell Is Nothing Then Exit Sub 'user clicked Cancel
        rCell.Worksheet.Activate
        rCell(1, 1).Show
        rCell(1, 1) = "Zombie Cell"
        rCell(1, 2) = "Dependent Cell"
        rCell(1, 3) = "Dependent Formula"
        For n = 1 To k
            rCell(n + 1, 1).Formula = "=HYPERLINK(""#" & Zombies(n) & """)"
            rCell(n + 1, 2).Formula = "=HYPERLINK(""#" _
                & SheetAddress(Dependents(n)) & """)"
            rCell(n + 1, 3) = "'" & Dependents(n).Formula
        Next n
    End If
End Sub

Function SheetAddress(Target As Range) As String
    'Remove workbook name from external range address
    SheetAddress = Replace(Target.Address(External:=True), _
        "[" & Target.Parent.Parent.Name & "]", "", 1, 1)
End Function

For each worksheet of the active workbook, the macro will find any merged cells and trace any zombie dependent formulas that might be on the local worksheet or on any other worksheet in the workbook. This macro resolves all of the issues described in my previous comment below except for the final pair.
Here is a sample of the results (see Figure 1 below) . Notice each cell is hyperlinked; a zombie cell's hyperlink accesses its merged cell.

The Tip's macro references ThisWorkbook, which is the location of the macro; that will be a problem if the macro is relocated to Personal.xlsb or an add-in. To avoid the problem, the following statement
    For Each ws In ThisWorkbook.Worksheets
should be replaced by this statement
    For Each ws In ActiveWorkbook.Worksheets
The macro uses red fill color to mark suspect formulas, but first it uses the following statement to clear the fill color of all cells in each worksheet
        ws.Cells.Interior.ColorIndex = xlNone
This is probably unnecessary because red fill color is somewhat unusual; therefore, that statement could be deleted. But if it is deleted, then the following comment statement
            ' Loop through formula cells
should be replaced by these statements
            ' Loop through formula cells
            If formulaCell.Interior.Color = vbRed Then _
                formulaCell.Interior.ColorIndex = xlNone
The macro uses InStr(1, X, Y, vbTextCompare) > 0 to identify a suspect formula, where X is the formula's text after removing any $ characters, Y is the relative address of a merged cell, and vbTextCompare ignores alphabetic case. Since Excel always capitalizes range addresses in formulas, it would be better to use vbBinaryCompare (case-sensitive). However, for a merged cell like D3 the macro cannot discriminate between any of the following possible formulas:
    =D3
    =D31
    =A1 & "Apartment D31"
    ="Vitamin D3" & B1
If D3 is a merged cell on Sheet1, the macro does not identify formulas on any other worksheet that reference Sheet1!D3; it only identifies formulas on Sheet1 that reference that sheet's D3 (or D31 or "Vitamin D3").
Finally, here are two issues that are more difficult to address:
1. If MyName is a named range that references merged cell D3, the macro does not identify a formula that references MyName.
2. If D3 is a merged cell on Sheet1 in Book1.xlsx, the macro does not identify a formula in another workbook that references [Book1.xlsx]Sheet1!D3.

A couple of tips, not mentioned here, but may help:

1. In an empty cell type "=", then select all cells (button above row numbers and left of column letters) and hit enter. Excel will issue a warning about circular references - click OK. Re-select the same cell and click Formulas/TracePrecedents twice and all the links on the sheet are shown with blue arrows. Clicking ShowFormulas will now identify links to empty cells (e.g. cells that have been over-written by merging), and provide further information about links to other worksheets.

2. To find links to other sheets - search for "=*!" (without the quote marks), select workbook and FindAll. All the external links will be listed.

May be difficult see clearly in an extremely complicated workbook, but worth a try.