Excel: list ranges targeted by INDIRECT formulas

Question

We have a few very large Excel workbooks (dozens of tabs, over a MB each, very complex calculations) with many dozens, perhaps hundreds of formulas that use the dreaded INDIRECT function. These formulas are spread out throughout the workbook, and target several tables of data to look-up for values.

Now I need to move the ranges of data that are targeted by these formulas to a different location in the same workbook.

(The reason is not particularly relevant, but interesting on its own. We need to run these things in Excel Calculation Services and the latency hit of loading each of the rather large tables one at a time proved to be unacceptably high. We are moving the tables in a contiguous range so we can load them all in one shot.)

Is there any way to locate all the INDIRECT formulas that currently refer to the tables we want to move?

I don't need to do this on-line. I'll happily take something that takes 4 hours to run as long as it is reliable.

Be aware that the .Precedent, .Dependent, etc methods only track direct formulas.

(Also, rewriting the spreadsheets in whatever is not an option for us).

Thanks!

Answer 1

You could iterate over the entire Workbook using vba (i've included the code from @PabloG and @euro-micelli ):

Sub iterateOverWorkbook()
For Each i In ThisWorkbook.Worksheets
    Set rRng = i.UsedRange
    For Each j In rRng
        If (Not IsEmpty(j)) Then
            If (j.HasFormula) Then
                If InStr(oCell.Formula, "INDIRECT") Then
                    j.Value = Replace(j.Formula, "INDIRECT(D4)", "INDIRECT(C4)")
                End If
            End If
        End If
    Next j
Next i
End Sub

This example substitues every occurrence of "indirect(D4)" with "indirect(C4)". You can easily swap the replace-function with something more sophisticated, if you have more complicated indirect-functions. Performance is not that bad, even for bigger Workbooks.