在Google电子表格中,是否可以根据条件的结果对一系列数字进行单元格数据验证?
例:
column1 column2
a if column1=a then show dropdown of (1,0) if b then (2,3) so [1,0]
a if column1=a then show dropdown of (1,0) if b then (2,3) so [1,0]
b if column1=a then show dropdown of (1,0) if b then (2,3) so [2,0]
a if column1=a then show dropdown of (1,0) if b then (2,3) so [1,0]
我不太清楚你想要什么,但你可以根据不同的值具有不同的下拉列表一个单元格。此代码假定要计算的值在单元格 A1("a"、"b"或其他值(。如果 A1=a,则下拉列表来自 C1:C2。如果 A1=b,则下拉列表列表来自 D1:D2。A1 中的任何其他值都会删除在B1中评估。(在 A1 中输入 a 或 b 将把回归(。
function onEdit(e)
{
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var sheetName= sheet.getSheetName();
if(sheetName=="Sheet1"){ //Only Sheet1
var editRange = sheet.getActiveRange();
var editRow = editRange.getRow();
var editCol = editRange.getColumn();
var lr = sheet.getLastRow()
var range = sheet.getRange("A1"); // Only apply on edit to cell Sheet1!A1
var rangeRowStart = range.getRow();
var rangeRowEnd = rangeRowStart + range.getHeight()-1;
var rangeColStart = range.getColumn();
var rangeColEnd = rangeColStart + range.getWidth()-1;
if (editRow >= rangeRowStart && editRow <= rangeRowEnd
&& editCol >= rangeColStart && editCol <= rangeColEnd)
{
drop()
}}}
function drop(){
var cell = SpreadsheetApp.getActive().getRange('B1');//set validation in B1
var ocell=SpreadsheetApp.getActive().getRange('B1').offset(0, -1).getValue()//evaluate value in A1
var cellVal=cell.getValue()
if(ocell=="a"){ //If A1=a dropdown list is C1:C2
var range = SpreadsheetApp.getActive().getRange('C1:C2');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule)}
else if(ocell=="b"){ //if A1=b dropdown list = D1:D2
var range = SpreadsheetApp.getActive().getRange('D1:D2');
var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();
cell.setDataValidation(rule);}
else{ // If A1 is not a or b remove validation from B1
cell.clearDataValidations()}
}
如果这不是你要找的,请解释,我可以调整。