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:

CodePudding user response：

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:

function onEdit(){
  //This method runs everytime a cell is modified
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ws = ss.getActiveSheet();
  
  var activeCell = ws.getActiveCell();
  
  if (activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ws.getSheetName() == "Stock") { //Only runs if the value changed is on the first column of Stock except first row
    var wsProducts = ss.getSheetByName("Products");
    var selectedValue = activeCell.getValue();

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

    //Get product Range column from Products based on Code
    var products = wsProducts.getDataRange().getValues();
    var rangeProduct = "";

    for (row of products) { //looping through Products and storing Range value in rangeProduct
      if (row[0] == selectedValue) {
        rangeProduct = row[3];
        break
      }
    }
    
    //Looping through defined Named Ranges to identify and set drop-down list
    var namedRangeList = ss.getNamedRanges();
    for (namedRange of namedRangeList) {
      if (namedRange.getName() == rangeProduct) { //if the Range name defined in Products is the same as Named Range name...
        var rangeSizes = namedRange.getRange();

        activeCell.offset(0, 3).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, 3).setDataValidation(validationRule); //Setting DataValidation object as value of column D from the modified row

      }
    }
  }
}