Drop-down dependent menus in Google Spreadsheets

0
votes

I am new to this formula milarky and have made a spreadsheet on Google sheets for my farm. I want to make the Herbicide(column G) drop down dependent on what is selected in column C.

my requirements for each individual crop is listed on dropdown tab(colm M-V)

(https://docs.google.com/spreadsheets/d/1wBaHVORL86C38FO1sRapxR3Ps9TuYfAFRzH12daN64k/edit?usp=sharing)

ps: any other ideas that anybody thinks would benifit on my sheet for me and my farm would be greatly appreciated

2
google-sheets
This post is not really about Java. Please user the correct tag, because it helps people find the question. It also helps you to get the attention of the right people, who might know how to help you. – zvone
I can definitely write a nested conditional formula, I just need a little more direction, as far as exact examples of what value you want to be represented. You can bring more than one conditional into the case, but can you please provide a little more example for expected output? – Aurielle Perlmann

2 Answers

0
votes

Attached is a link to the revision I made to your spreadsheet. I added a Sheet4 which reads your Dropdown List sheet. The Herbicides Needed dropdown I added then reads Sheet4. Take a look at it and if you have any questions let me know.

https://docs.google.com/spreadsheets/d/1P2hKDqb-MtOW5Ov3MZiac8LMQLwSV-k4b1R8SIUFyeE/edit?usp=sharing

0
votes

I answered this question about six months ago on YouTube (https://youtu.be/rW9T4XZy-7U). The classical way to do it, where in column A you have the main drop down list, and in column B you have the second drop down list which is dependent on the first in column A, is below. I have also added script to be able to have a second dependent drop down list in column C dependent on column B. This script requires that you have named ranges already set up within the spreadsheet, otherwise it will not work.

function depDrop_(range, sourceRange){
 var rule = SpreadsheetApp.newDataValidation().requireValueInRange(sourceRange, true).build();
 range.setDataValidation(rule);
}

function onEdit (){
  var aCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var aRow = aCell.getRow();
  var aColumn = aCell.getColumn();

if (aColumn == 1 && SpreadsheetApp.getActiveSheet()){
  var range = SpreadsheetApp.getActiveSheet().getRange(aRow, aColumn + 1);
  var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
  depDrop_(range, sourceRange);
}

else if (aColumn == 2 && SpreadsheetApp.getActiveSheet()){
  var range = SpreadsheetApp.getActiveSheet().getRange(aRow, aColumn + 1);
  var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
  depDrop_(range, sourceRange);
}
}

The less common way to put these drop down lists is by rows, meaning that the main drop down list is in A1 for example, and the dependent drop down list is in A2. To achieve this, you only need to change "aColumn" to "aRow" at the beginning of the if clauses and put + 1 after "aRow" instead of the "aColumn" in the middle of the if clauses. For example:

if (aRow == 1 && SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1")){
var range = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").getRange(aRow + 1, aColumn);
var sourceRange = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(aCell.getValue());
depDrop_(range, sourceRange);
}