Is it possible to mass edit formulas in Google Sheets?

1
votes

Is there a way to speed up the editing of hundreds of lines of formulas across many tabs in the same Google Sheet?

This is a followup question to: References changing in Google Sheets with new forms submissions ; 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 $ (Still no idea why that happens). The solution is to add INDIRECT to the formulas. However, there are too many formulas to comfortably change manually.

For example I have formulas like:

   =COUNTIFS('Resp'!$AM$56:$AM,"Conf",'Resp'!$B56:$B,"PC 
   Arr")

It needs to change to :

  =COUNTIFS(INDIRECT("Resp!$AM$2:$AM"),"Conf",indirect("Resp!$B2:$B"),"PC 
   Arr")

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)
and the " ' " in the middles need to disappear.

Is there a way to speed up these changes even a bit or is it inevitable manual labor?

3
google-sheetseditgoogle-sheets-formulaarray-formulas
If you don't mind using Google Apps Script, you could use the getFormula() function to fetch the formulas as strings and then change them. – ADW

3 Answers

4
votes

the only way how to mass-change formulas in Google Sheets is via CTRL + H where you can do it like this:

0

1
votes

I found a mechanical way to lessen the amount of work to change many different formulas, but let me start with a general case first.

Option 1- For when the same formula repeats identically.

Search and replace (Ctrl+H) works well if you have the same formulas repeated all over. In this case it's viable to change massively, or once and then drag as needed.

However...

Option 2- When you have dozens of formulas that may be just slightly different or just too similar that 'Search and replace' (Ctrl+H) wouldn't work.

More so, if it's just so different that you cant drag the formula (like the reference changing to a new column and writing a different text based on that), then unfortunately you need to manually change things.

The solution I'm using to lessen the work was to use a programmable keyboard (in my case programmable+multimedia keyboard - Steren COM-6490 , up to 15 macros). I used it to write down: INDIRECT(" , ") and $2 mainly, and then all the other mayor parts of text,references or formulas that do repeat with just 1 key each. Making a duplicate of your current formula on another sheet and have it open too in order to copy-paste stuff to complement the formulas also helps.

0
votes

You may be able to write a regular expression to do so, but think about how you know which ones to change, and how you can make a pattern to tell the computer what to change and what to. You could also write a script if you can do that (it can be found in the tools section).