1

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.

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

  • For me using the script is actually the best solution, thank you for your help! – Marek Marhoul Jun 14 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.