ArrayFormula stops working after shuffling rows

Question

I have a pretty extensive sheet wherein many columns I use an array formula to automatically fill data in from various other spreadsheets. The problem I have is when I need to rearrange rows then what is in that row output from array formula stays there as plain text after moving and that prevents the array formula from working because it cannot overwrite data in those cells that now contain text.

Is there a workaround? I don't want to constantly need to delete the cells manually many times a day.

Answer 1

The workaround would be to temporarily disable ARRAYFORMULA by removing = sign or inserting ' in front of = sign - then do your row swapping and then again fix your array formula.

The second workaround would be to delete the static value after you moved your row so the array formula could roll out instead of posting #REF! error.

The 3rd option includes a script that will delete the given range(s).

function moveValuesOnly() { var ss = SpreadsheetApp.getActiveSpreadsheet();
                            var source = ss.getRange('Sheet1!B10');
                            source.copyTo(ss.getRange('Sheet1!AO3:AO'), 
                            {contentsOnly: true}); }

or:

function clearArrays() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  var rangesToClear = ['AO3:AO', 'AR4:AR8'];
  for (var i=0; i < rangesToClear.length; i++) { 
    sheet.getRange(rangesToClear[i]).clearContent();
  }
}