Google Sheet: Show first data validation value if cell is empty

Question

Good afternoon everyone,

I am looking for a way for my dropdown list to show the first value if the cells A18 and F18 are empty. The data validation range is from cell X18 to AA18 (4 values).

So basically, when I enter the name 'Test', it will fetch the data: "Healer", "Tank", "Paladin" and "Mixed DPS" from another tab and display it in the cells X18 to AA18. Using data validation a dropdown list is being generated with these values. However, as soon as I enter a name, I want the default value of the dropdown list to be the value of X18.

So: if A18 is empty, the value of F18 should be matching X18. (if no name is given, there wont be anything in F18 so it should stay empty)

Answer 1

If I understand correctly just put the formula in Cells F18:F.

=IF($A18<>"",$X18,"")

If column A is not empty then fetch the value in column X, otherwise blank (or select from dropdown). Take a look at the sheet you linked, I have updated it.

Selecting from the dropdown will overwrite the formula, if this is the issue you're referring to your only fix is to create a helper column or utilize scripts.

UPDATE

In order to accomplish this with scripts you can add the following:

function onEdit(e) {

  var editRange = { // A18:A250
    top : 18,
    bottom : 250,
    left : 1,
    right : 1
  };

    //Get sheet from which the event occured
  var range = e.range
  var sheet = range.getSheet();  

    // Exit if wrong sheet
  if (sheet.getSheetName() != "War Board") return;

    // Exit if we're out of range
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;

  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;

  //Set default if value is not blank
  if (!range.isBlank()) range.offset(0, 5).setValue(range.offset(0, 23).getValue());

  //Set blank if value is blank
  if (range.isBlank()) range.offset(0, 5).setValue("");
}

This will set the value of F to the corresponding value in X column if a name is given in column A. If the cell in column A is emptied it empties cell F.