Google Sheets - Dependent drop-down lists

Question

I am recreating and expanding on a doc I had previously made. I have already brought in the script I had used originally, and tweaked it where I believed appropriate to get it working in this sheet, but I must have missed something. Editable samples of the 3 spreadsheet files involved can be found here. These files are a sample "Price List", "Catalog"(which aggregates manufacturer names from all price lists, and also has a "Catalog" tab for misc items not sold by one of my primary vendors), and "Addendum B" which is the file I require assistance with.

This document is an addendum to my contracts which lists all equipment being sold as part of that contract. It has 2 sheets in it ("Addendum B" and "XREF"), and "Addendum B" has several dependent columns: Vendor, Manufacturer, Model, Description, and Price. Their dependencies are as follows:

Currently Working

Vendor: Basic data validation pulling from XREF!A2:A.

Not working, script in file

Manufacturer: Based on the Vendor selected, should be a drop-down list generated from the column headed with that vendor's name on "XREF".

Now here's were it gets tricky beyond what I had previously done.

Model: I want this column to be a drop-down listing all model numbers associated with that manufacturer, from a completely separate price list provided to me by my vendor. (I have shared a sample price list which reflects column positions as they appear in all such files.
Description: Displays the corresponding description for the Model selected, from the price list selected in the Vendor column.
Price: Displays the corresponding markup price for the Model selected, from the price list selected in the Vendor column.

And that about summarizes my goals and what I'm struggling with.

Final note, you will get a better response if you include your code and restrict yourself from asking multiple questions. Including code shows you put effort into the problem and asking just one/related question keeps the problem tractable. Also, prevents the question from getting closed for being too board. — Jack Brown

Jack Brown Jack Brown · Accepted Answer · 2017-03-14T23:59:10

So I looked into your script file in sheet Addendum B. I have made few edits and it should be working now, the modified code:

    function onEdit() 
{
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      sheet = ss.getActiveSheet(),
      name = sheet.getName();
  if (name != 'Addendum B') return;
  var range = sheet.getActiveRange(),
      col = range.getColumn();
  if (col != 6) return;  //You col was set to 5 changed it to 6!
  var val = range.getValue(),
      dv = ss.getSheetByName('XREF'),
      data = dv.getDataRange().getValues(),
      catCol = data[0].indexOf(val),
      list = [];
  Logger.log(catCol)
  for (var i = 1, len = 100; i < len; i++) // Problem is here, you have too many items in list! Cannot have more 500 items for validation
      list.push(data[i][catCol]);
  var listRange = dv.getRange(2,catCol +1,dv.getLastRow() - 1, 1)
  Logger.log(list)
  var cell = sheet.getRange(range.getRow(), col-1)
  var rule = SpreadsheetApp.newDataValidation()
  .requireValueInRange(listRange)   // Use requireValueIn Range instead to fix the problem
  .build();
  cell.setDataValidation(rule);
  Logger.log(cell.getRow())
}

The reason your validation was not working was you had more than 500 items in your data validation list. I just modified it to take the same values from range instead. Hope you find that helpful!

Now for the remaining 3 questions, here are my comments and thoughts on it:

1) I didn't find any code related to the problem you mentioned in your question. So, I am gonna assume you are asking for general ideas on how to achieve this?

2) You basically approach the problem the same as you did with the above code! Once a manufacturer is selected, the script looks for that manufacturer in the sheet and update the Data validation in the corresponding model column. You will modify the code like so

var ss = SpreadsheetApp.openById("1nbCJOkpIQxnn71sJPj6X4KaahROP5cMg1SI9xIeJdvY")
//The above code with select the catalog sheet.
dv = ss.getSheetByName('Misc_Catalog')
//The above code will open the Misc_Catalog tab.

3) A better approach would be to use Sidebar/Dialog Box to validate your input then add it to the sheet at the end. (Looks Cleaner and also prevents unnecessary on edit trigger in the sheet, which can take a while to update.) You find more details here: https://developers.google.com/apps-script/guides/dialogs

Google Sheets - Dependent drop-down lists

1 Answers