Google Sheets - Combine VLOOKUP with Named Ranges to create Drop Down

Question

I have 3 worksheets - Stocks, Products, and Sizes.

I have created 3 named ranges: 'Adults', 'Kids' and 'Shoes'.

On the Stock worksheet, once a product has been added - I'd like the size column to become a drop down containing the correct sizes for that product - based on the named range set in Products.

EXAMPLE

Product 'redshirt1' in Products has been assigned a range of 'Adults'. Therefore, the following cell in Stock should contain a drop down with the following sizes: L, XL, XXL

I hope that's clear enough, thanks in advance for any help.

Here is my Google Sheet:

https://docs.google.com/spreadsheets/d/1bxmItQpOOQ-WTBsd0uA0WmzYACVUdtCVRaex879KgcE/edit?usp=sharing

Answer 1

Try

function onEdit(e){
  var sh = e.source.getActiveSheet()
  for (var i = e.range.rowStart;i<=e.range.rowEnd;i++){
    for (var j = e.range.columnStart;j<=e.range.columnEnd;j++){
      validationManagement(sh,sh.getRange(i,j))
    }
  }
}
function validationManagement(sh,cel){
  if (sh.getName()=='Pending' && cel.getRow()>1 && cel.getColumn()==10){
    if (cel.getValue()==''){
      cel.offset(0, 2).clearContent().clearDataValidations();
    }else{
      var ss = SpreadsheetApp.getActiveSpreadsheet()
      var styles = ss.getSheetByName('Styles').getDataRange().getValues().filter( r => (r[0]==cel.getValue()) )
      var sizes = ss.getSheetByName('Sizes')
      var col = sizes.getRange(1,1,1,sizes.getLastColumn()).getValues().flat().indexOf(styles[0][2])+1
      cel.offset(0, 2).clearContent().clearDataValidations(); 
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(sizes.getRange(2,col,sizes.getLastRow()-1,1)).build()
      cel.offset(0, 2).setDataValidation(validationRule);
    }
  }
}

This will allow you to change multiple values at once.

https://docs.google.com/spreadsheets/d/1tfs3fIj8pgMkmfySzbXUCqgXainG2o1rchLg4foVZeo/edit?usp=sharing

references

• onEdit
• filter
• flat
• indexOf
• DataValidationBuilder

Answer 2

The following formula would also return the correct Named Range according to the Range defined in Products:

=INDIRECT(IFNA(VLOOKUP(A2:A,Products!A2:Z,4, FALSE), ""))

However, Data Validation lists in Google Sheets don't accept formulas as input. In order to avoid having to create a "Helper column" for each row in Stock to populate the drop-down list, using Apps Scripts, you can achieve that with the Simple Trigger onEdit() to populate the Sizes column depending on the selected "Code".

Sample code (edit):

function onEdit(){
  //This method runs every time a cell is modified
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getActiveSheet();
  
  var activeCell = ws.getActiveCell();

  if (activeCell.getColumn() == 10 && activeCell.getRow() > 1 && ws.getSheetName() == "Pending") { //Only runs if the value changed is on the "J" column of Pending except first row
    var wsStyles = ss.getSheetByName("Styles");
    var selectedValue = activeCell.getValue();

    activeCell.offset(0, 2).clearContent().clearDataValidations().setValue("Loading...");

    //Get style Range column from Styles based on Code
    var styles = wsStyles.getDataRange().getValues();
    var rangeStyle = "";

    for (row of styles) { //looping through Styles and storing Range value in rangeStyle
      if (row[0] == selectedValue) {
        //Slightly edit here, since the Size name defined in Styles contains spaces, which Named Ranges doesn't accept as valid
        //Make sure to declare your named ranges exactly as declared in "Styles" but replacing any spaces with "_" in order for this code to run
        rangeStyle = row[2].replace(" ", "_"); 
        break
      }
    }
    
    //Looping through defined Named Ranges to identify and set drop-down list
    var namedRangeList = ss.getNamedRanges();
    for (namedRange of namedRangeList) {
      if (namedRange.getName() == rangeStyle) { //if the Range name defined in Styles is the same as Named Range name...
        var rangeSizes = namedRange.getRange();

        activeCell.offset(0, 2).clearContent().clearDataValidations(); //cleaning column D from the modified row
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(rangeSizes).build(); //Instantiating DataValidation object with the range of data retrieved previously
        activeCell.offset(0, 2).setDataValidation(validationRule); //Setting DataValidation object as value of column D from the modified row

      }
    }
  }
}

• EDIT: Make sure to declare your Named Ranges replacing spaces with "_" for this code to work on the new Sheet provided.

Answer 3

you can get this with:

=INDEX(IFNA(VLOOKUP(VLOOKUP(A2:A, Products!A1:D, 4, 0), 
 SPLIT(SUBSTITUTE(TRIM(FLATTEN(
 QUERY(Sizes!A1:C,,9^9))), " ", "×", 1), "×"), 2, 0)))

but to turn it into dropdowns try: