Google电子表格中的下拉菜单 [英] Drop-down dependent menus in Google Spreadsheets

问题描述

我对这个配方milarky很陌生，并在我的农场的Google工作表上制作了一个电子表格。我想让除草剂（G列）下降，这取决于列C中的选择。

我对每个单独作物的要求在下拉列表中列出（colm MV ）

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

ps：任何人认为的其他想法benifit在我的表上对我和我的农场将不胜感激

解决方案

我在六个月前在YouTube上回答了这个问题（< a href =https://youtu.be/rW9T4XZy-7U =nofollow> https://youtu.be/rW9T4XZy-7U ）。经典的做法是，在A列中你有主下拉列表，B列中有第二个下拉列表，它依赖于列A中的第一个，如下所示。我还添加了脚本，以便能够在C列中拥有依赖于列B的第二个从属下拉列表。此脚本要求您已在电子表格中设置了已命名的范围，否则它将无法工作。

 函数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（）; （aColumn == 1&&; SpreadsheetApp.getActiveSheet（））{
 var range = SpreadsheetApp.getActiveSheet（）。getRange（aRow，aColumn + 1）; 
 
 if 
 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）; 
} 
} 
  

放置这些下拉列表的不常用方法是按行排列，这意味着主下拉列表在A1中，而从属下拉列表在A2中。为了达到这个目的，你只需要在if子句的开始处将aColumn改为aRow，并将aRow之后的+1替换为if子句中间的aColumn。例如：

  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）; 
} 
  

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

解决方案

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);
}

这篇关于Google电子表格中的下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！