<p>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.</p>

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

<p>(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.)</p>

<p><strong>Is there any way to locate all the INDIRECT formulas that currently refer to the tables we want to move?</strong></p>

<p>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.</p>

<p>Be aware that the .Precedent, .Dependent, etc methods only track direct formulas.</p>

<p>(Also, rewriting the spreadsheets in <em>whatever</em> is not an option for us).</p>

<p>Thanks!</p>

[{'answer_id': 34719, 'author': 'sdfx', 'author_id': 3445, 'author_profile': 'https://Stackoverflow.com/users/3445', 'pm_score': 4, 'selected': True, 'text': '<p>You could iterate over the entire Workbook using vba (i\'ve included the code from @PabloG and @euro-micelli ):</p>\n\n<pre><code>Sub iterateOverWorkbook()\nFor Each i In ThisWorkbook.Worksheets\n    Set rRng = i.UsedRange\n    For Each j In rRng\n        If (Not IsEmpty(j)) Then\n            If (j.HasFormula) Then\n                If InStr(oCell.Formula, "INDIRECT") Then\n                    j.Value = Replace(j.Formula, "INDIRECT(D4)", "INDIRECT(C4)")\n                End If\n            End If\n        End If\n    Next j\nNext i\nEnd Sub\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 34750, 'author': 'PabloG', 'author_id': 394, 'author_profile': 'https://Stackoverflow.com/users/394', 'pm_score': 0, 'selected': False, 'text': '<p>You can use something like this in VBA:</p>\n\n<pre><code>Sub ListIndirectRef()\n\nDim rRng As Range\nDim oSh As Worksheet\nDim oCell As Range\n\nFor Each oSh In ThisWorkbook.Worksheets\n    Set rRng = oSh.UsedRange\n    For Each oCell In rRng\n        If InStr(oCell.Formula, "INDIRECT") Then\n            Debug.Print oCell.Address, oCell.Formula\n        End If\n    Next\nNext\n\nEnd Sub\n</code></pre>\n\n<p>Instead of Debug.Print you can add code to suit your taste</p>\n'}, {'answer_id': 35042, 'author': 'sdfx', 'author_id': 3445, 'author_profile': 'https://Stackoverflow.com/users/3445', 'pm_score': 0, 'selected': False, 'text': '<blockquote>\n  <p>Unfortunately, the arguments of\n  INDIRECT are usually more complex than\n  that. Here\'s an actual formula from\n  one of the sheets, not the most\n  complex formula we have:</p>\n  \n  <p><code>=IF(INDIRECT("\'"&amp;$B$5&amp;"\'!"&amp;$O5&amp;"1")="","",INDIRECT("\'"&amp;$B$5&amp;"\'!"&amp;$O5&amp;"1"))</code></p>\n</blockquote>\n\n<p>hm, you could write a simple parser by ignoring most of the characters and just looking for the relevant parts (in this example: "A..Z", "0..9" and "!:" etc.) but you will run into troubles if the arguments in "indirect" are functions.</p>\n\n<p>maybe the safer approach would be to print every occurence of "indirect" in a third sheet. you could then add the desired output and write a small search and replace program to write your changes back.</p>\n\n<blockquote>\n  <p>If you "get" every cell in a huge\n  spreadsheet you might end up needing\n  monstrous amounts of memory. I am\n  still willing to try and take that\n  risk.</p>\n</blockquote>\n\n<p>PabloG\'s method of selecting the used range is the way to go (added it into my original code). The speed is pretty good, especially if you check whether the current cell contains a formula. Obviously, this all depends on the size of your workbook.</p>\n'}, {'answer_id': 67261, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>Q: "Is there any way to locate all the INDIRECT formulas that currently refer to the tables we want to move?"</p>\n\n<p>As I read it, you want to look inside the arguments of INDIRECT for references to areas of interest.\nOTTOMH I\'d write VBA to use a regular expression parser, or even a simple INSTR to find INDIRECT(  read forward to the matching ), then EVALUATE() the string inside to convert it to the actual address, repeat as required for multiple INDIRECT(...) calls and dump the formula and its translation to two columns in a sheet.</p>\n'}, {'answer_id': 1429203, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>I'm not sure what the etiquette of SO is concerning mention of products with which the writer is connected, but OAK, the Operis Analysis Kit, an Excel add-in, can replace the INDIRECT functions by the cell references they resolve to.  You can then use Excel's audit tools to determine what dependents each range has.  </p>\n\n<p>You would, of course, do this to a temporary copy of the workbook.</p>\n\n<p>More at</p>\n\n<ul>\n<li>http://www.operisanalysiskit.com/oakpruning.htm</li>\n<li>http://www.operisanalysiskit.com/help/2007/index.html?oakconceptpruning.htm</li>\n</ul>\n\n<p>Given the age of this question you may well have found an alternative solution or workaround.</p>\n"}]
['https://Stackoverflow.com/questions/34493', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2230/']