Output array formula, ignoring cells in output area that already contain data-CodePudding

I'm trying to work on a Google Sheet and make it as user friendly as possible, so when I'm not around others can't screw it up. I have previously copied the formula down the column, but I'm trying to do it in an array formula, that is in the header of the column so people don't need to copy the formula to other cells.

The formula is simply a VLOOKUP, comparing a cell to the left with the code, and retrieving the title and other information later. (I've had this working happily via copying the formula down the column.)

VLOOKUP($F$2:F, 'Lessons NEW'!$E$2:$F,2,false)

My problem comes when trying to do this using an array formula in the header. This also works properly, but doesn't allow me to do somethings needed.

={"Title";
ARRAYFORMULA(
  IF($F2:$F<>"",
    VLOOKUP($F$2:F, 'Lessons NEW'!$E$2:$F,2,false),
  )
)}

Again this works happily. HOWEVER. my issue is that on some rows I need to manually enter some info.

What I want to happen, is rows in the column that have data, in the array formula's destination to get skipped over and simply ignored during the array formula's output and end up containing what's been manually entered in them. (Some rows in the column will just be random manual entry stuff)

I've tried doing this via checking if there is a code in the cell to the left, and try to make it skip if so, or check if the destination cell has contents already and skip if so. but it seems that if I have anything in the output area of the array formula, it breaks it completely.

Does anyone have any suggestions of how I can accomplish this? Thanks!

CodePudding user response：

Unfortunately there's no direct solution within ARRAYFORMULA. If you can, and ideally there are no in between rows added, you can add a column to the left to be hidden, and contain the formula adapted:

={"",VLOOKUP($F2, 'Lessons NEW'!$E$2:$F,2,false)}

Without ARRAYFORMULA it will be able to expand at any row that doesn't have a handwritten value. You may also protect that whole hidden column (that will mean also that no in between rows will be added by noon authorised people, may be also useful for you)

UPDATE

Sample script for copying the formula. Adapt the ranges and name of the sheet:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  SpreadsheetApp.getUi().createMenu("Custom")
    .addItem("Copy Formula","copyformula")
  .addToUi()

  copyformula()
}

function copyformula(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sh = ss.getSheetByName("Sheet3")
  var range = sh.getRange("D1")
  range.copyTo(sh.getRange("D2:D"))


}

CodePudding user response：

what you describe contradicts the intended usage of arrayformula. arrayformula is designed to roll out and for that, it needs an empty space. any manual input in that empty space will render the arrayformula out of the game. there are only two solutions for your issue:

not use arrayformula but VLOOKUP formula in every cell
use a script that will inject the formula only if there is no manual input
and one more hybrid solution - have your arrayformula in one column and your manual input in the next column, and then have 3rd column that will gather the data like if manual column is empty output vlookup otherwise output manual input