Finding References to Nonexistent Cells

Gail can easily merge two cells, such as C3:D3, into a single cell. However, if she has any formulas in the worksheet that reference cell D3, after the merge they still refer to cell D3, which is meaningless. Gail wonders how she can locate any formulas that refer to the now nonexistent cell D3 so she can determine how to best edit the formulas.

Gail is correct—if you merge cells that are referenced by formulas, that can result in some weird effects in your formulas. Using Gail's example, there are four possible conditions of cells C3 and D3—each of them can be either empty or contain something. For this analysis, I'm going to assume that the "something" is a numeric value.

C3 has a value; D3 is empty. Merging C3 and D3 results in references to both C3 and D3 remaining intact. Since the value in D3 is thrown away upon merging, after-merge references to D3 are equal to 0.

C3 has a value; D3 has a value. Merging C3 and D3 results in references to both C3 and D3 remaining intact. Since the value in D3 is thrown away upon merging, after-merge references to D3 are equal to 0.

C3 is empty; D3 is empty. Merging C3 and D3 results in references to both C3 and D3 remaining intact. Since D3 was empty before the merge, there was nothing to throw away and the after-merge references to D3 are equal to 0.

C3 is empty; D3 has a value. Merging C3 and D3 results in references to C3 being replaced by a #REF error. References to D3 are replaced with references to C3. Since the value originally in D3 is now in C3 (the merged C3:D3), formulas previously referencing D3 now reference C3, post merge, and work fine.

Note that in three of the scenarios, references to D3 remain intact. In only one scenario are references to D3 changed to C3. Because of this, the easiest way to figure out if there will be problems post-merge is to do your checking before you do the merging. The auditing tools provided by Excel can be very helpful in this endeavor. You first select cell C3 and check for dependent cells, then repeat the process with cell D3. You can find more information about the auditing tools and checking for dependents here:

https://excelribbon.tips.net/T8626

Once you know where the dependencies are, you can make any appropriate formula changes keeping the effects in mind that are detailed above.

If the cells were already merged, then you can try a couple of things. First, you can try using Find and Replace. Follow these steps:

1. Press Ctrl+F. Excel displays the Find tab of the Find and Replace dialog box.
2. In the Find What box, enter D3.
3. Click Find All.

That's it. The result is a list, in the Find and Replace dialog box, of all cells that contain D3. Note that this is not just D3 as a cell reference, but any usage of D3, i.e., in text. This includes references to D31, D32, etc. You can see in the results, though, if the cells matched contain a formula which can help you determine whether to investigate that cell further.

Note that you should also search for variations on the D3 reference using D$3. (You don't need to search for $D3 or $D$3 because D3 and D$3 will match them.)

If you prefer a macro-based solution, you can consider the following macro. Run it, and every merged range in every worksheet in the workbook is checked to see if there are formulas that refer to anything except the upper-left corner of the merged area. If such formulas are found, then they are marked in red and can be easily spotted.

Sub HighlightFormulasWithIncorrectReferences()
    ' Highlights cells with formulas that don't refer to
    ' the top-left cell in merged ranges
    Dim ws As Worksheet
    Dim formulaCell As Range
    Dim mergedRange As Range
    Dim mergedAddress As String
    Dim insideCell As Range
    Dim cellAddress As String

    On Error Resume Next
    For Each ws In ThisWorkbook.Worksheets
        ' Clear any existing highlights of cells
        ws.Cells.Interior.ColorIndex = xlNone
        For Each formulaCell In ws.UsedRange.SpecialCells(xlCellTypeFormulas)
            ' Loop through formula cells
            For Each mergedRange In ws.UsedRange
                ' Loop through all merged areas
                If mergedRange.MergeCells Then
                    For Each insideCell In mergedRange.MergeArea.Cells
                        ' Loop through all cells in merged area EXCEPT top-left
                        If Not insideCell.Address = mergedRange.MergeArea.Cells(1, 1).Address Then
                            cellAddress = insideCell.Address(False, False)
                            If InStr(1, Replace(formulaCell.Formula, "$", ""), cellAddress, vbTextCompare) > 0 Then
                                formulaCell.Interior.Color = vbRed
                                Exit For
                            End If
                        End If
                    Next insideCell
                End If
            Next mergedRange
        Next formulaCell
    Next ws
    MsgBox "Marked formulas referring to not top-left cells inside merged ranges.", vbInformation
End Sub

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10439) applies to Microsoft Excel .