Geek Answers Handbook

Excel: List Ranges for INDIRECT Formulas

We have some very large Excel workbooks (dozens of tabs, each MB, very complex calculations) with many dozens, perhaps hundreds of formulas that use the awful INDIRECT function. These formulas are distributed throughout the workbook and focus on several data tables for finding values.

Now I need to move the data ranges that target these formulas to a different place in the same book.

(The reason is not significant, but interesting in itself. We need to run these things in Excel calculation services, and the delay in loading each of the rather large tables was sometimes unacceptably high. Move the tables in the adjacent range so that we can load them all for one shot.)

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

I do not need to do this online. I would love to take something that takes 4 hours to work as long as it is reliable.

Keep in mind that .Precedent, .Dependent, etc. methods only direct formulas are tracked.

(In addition, rewriting spreadsheets is something that is not an option for us).

Thanks!

+5
source share
5 answers

You can iterate over the whole book using vba (I 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

" (D4)" " (C4)". - , . , .

+5

Q: " INDIRECT, , ?"

, INDIRECT . OTTOMH VBA INSTR, INDIRECT ( ), EVALUATE() , , , INDIRECT (...) .

+1

- VBA:

Sub ListIndirectRef()

Dim rRng As Range
Dim oSh As Worksheet
Dim oCell As Range

For Each oSh In ThisWorkbook.Worksheets
    Set rRng = oSh.UsedRange
    For Each oCell In rRng
        If InStr(oCell.Formula, "INDIRECT") Then
            Debug.Print oCell.Address, oCell.Formula
        End If
    Next
Next

End Sub

Debug.Print ,

0

, , . , :

=IF(INDIRECT("'"&$B$5&"'!"&$O5&"1")="","",INDIRECT("'"&$B$5&"'!"&$O5&"1"))

hm, , ( : "A..Z", "0..9" "!:" ..). , "" .

, , "" . , .

"" , . .

PabloG - ( ). , , . , .

0

, SO , , OAK, Operis Analysis, Excel, INDIRECT , . Excel, , .

, , .

  • http://www.operisanalysiskit.com/oakpruning.htm
  • http://www.operisanalysiskit.com/help/2007/index.html?oakconceptpruning.htm

Given the age of this question, you may well find an alternative solution or workaround.

0
source

More articles:


All Articles