How to use Split array of a single cell for data validation dropdown?

1
votes

I am trying to create data validation dropdown from a single cell which has all , separated values and I am splitting them using SPLIT(K4,","). But when I apply the formula drop-down just goes away. Here is how it looks:

enter image description here

And here is where I have applied validation:

enter image description here

It just happens and I can't see any drop-down here. Even validation doesn't work as I type a value from given values, it still identifies it as invalid: enter image description here

Here it says that it is actually not possible, but otherwise, my data column will grow very big, that's why I wanted to keep it in a single cell.

Method to reproduce: Just make a copy of this sheet and experiment on your own whatever you want it to be like:

1
google-sheetsgoogle-sheets-formula
I think this is not possible. You can google it, there are many references to this issue. Your only option is to create a list of items and then reference to the range of this list. - soMarios

1 Answers

1
votes

It is not possible to do this from the sheets editor, but you could use Google Apps Script to accomplish this:

  • Open the script editor by selecting Tools > Script editor.
  • Copy and run this function:
function createDataValidation() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const values = sheet.getRange("A1").getValue().split(","); // Get array with values from A1
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(values); // Create DV
  sheet.getRange("F8").setDataValidation(rule); // Set DV to F8
}

Reference: