title: Is it possible to mass edit formulas in Google Sheets?
body: <p>Is there a way to speed up the editing of hundreds of lines of formulas across many tabs in the same Google Sheet?</p>
  
  <p>This is a followup question to: <a href="https://stackoverflow.com/questions/57260595/references-changing-in-google-sheets-with-new-forms-submissions">References changing in Google Sheets with new forms submissions</a> ; which is about every time a new row is added in Google Sheets by a new submission from a Google Forms, some formulas change their references even if they are fixed with <code>$</code> (Still no idea why that happens). The solution is to add <code>INDIRECT</code> to the formulas. However, there are too many formulas to comfortably change manually.</p>
  
  <p>For example I have formulas like:</p>
  
  <pre><code>   =COUNTIFS('Resp'!$AM$56:$AM,"Conf",'Resp'!$B56:$B,"PC 
     Arr")
  </code></pre>
  
  <p>It needs to change to :</p>
  
  <pre><code>  =COUNTIFS(INDIRECT("Resp!$AM$2:$AM"),"Conf",indirect("Resp!$B2:$B"),"PC 
     Arr")
  </code></pre>
  
  <p>Search and replace wouldn't work because the way formulas are written, and there are quite varied types of formula and referenced columns (although the reference must change from 56 to 2) <br>and the  " <code>'</code> " in the middles need to disappear.</p>
  
  <p>Is there a way to speed up these changes even a bit or is it inevitable manual labor?</p>