#REF! error on moving rows with ArrayFormula

Question

#REF! error in ArrayFormula as rows are moved

As I move rows (click and drag) in a sheet whose columns have an array formula in row 1, it seems that the values in the array formula are copied as "values". The ArrayFormula turns to #REF!

In this example, I have an array formula in cell T1. As I move row 5 (for instance) to row 11, I have this:

Error Array result was not expanded because it would overwrite data in T11.

in cell T1 I have #REF!
in cell T11 I now have a value
the rest of the column is now blank

Sorting works normally, however.

How to move rows around with an ArrayFormula on row 1 without generating any #REF! error???

player0 player0 · Accepted Answer · 2019-06-04T20:58:55

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();
  }
}

#REF! error on moving rows with ArrayFormula

1 Answers