限制Google表格中的下拉结果

Question

我在小型企业中担任运营经理，我正在尝试设置一个易于推销员使用的订单。在订单中，我使用了OFFSET函数来引用包含客户和价格的主列表。在客户下方，我键入客户，并通过自动完成下拉列表将其从主列表中抽取出来。产品也一样。

这里是订货单：

Order Sheet Example

我的问题，我会在产品开始键入如'虾'。我们有十几条虾线，但一个特定的客户只会拿一个。所有其他虾的结果都没有该客户的价格。但是，自动完成功能将提供所有'对虾'结果。

在主列表中，我只输入了特定客户使用的产品的价格。看看什么主列表的样子：

Master List look

没有什么客户需要什么，这是在试错的运动一个很好的记忆。在上面的例子中，我可以输入'顶部'，如果我选择了错误的一个，价格不会上涨。

这令人沮丧。

我一直希望有一种方法来限制自动完成，这样当我为那个客户输入'prawn'或'topps'时，它只会自动填写有价格的字段。谁能帮忙？还是有人知道任何解决方法？我会非常感激，目前的订单系统非常困难。

Answer 1

我相信这会做你想做的。从您的样本数据中有点难以分辨。它使用谷歌应用程序脚本和表名和/或柱可能需要在脚本中为您的数据进行更改。我附上一个示例电子表格，您可以复制然后尝试。您必须在您制作的副本中批准脚本。

function onEdit(e) { 
    var cust=e.value //The value of the edited cell 
    var sh=e.source.getActiveSheet().getSheetName()//Name of the active sheet. 
    var col=e.range.getColumn()//The edited column 
    var r=e.range.getRow()//The edited row 
    var row=e.range.offset(0,1).getA1Notation()//Cell A1 notation of cell in same row one column to the right. 
    var ss=SpreadsheetApp.getActiveSpreadsheet() 
    var s=ss.getActiveSheet().getSheetName()//Name of active sheet 
    var s1=ss.getSheetByName("Sheet1")//Variables for sheets 
    var s2=ss.getSheetByName("Sheet2") 
    var s3=ss.getSheetByName("TEMP") 
    var rng=s2.getDataRange().getValues()//Customer/Products 

    if(sh=="Sheet1" && col==1){//If sheet1 is active sheet and Customer (column A) is edited. 

    var array=[]//Array to hold customers products 
    for (var i=0;i<rng.length;i++){ 
    if(rng[i][0]==cust){ 
     for(var j=1;j<rng[0].length-1;j++){ 
     if(rng[i][j]!=""){//If customers product has $ entry add to Array. 
      array.push([rng[0][j]]) 
    }}}} 
    s3.clearContents()//Clear old product list from TEMP. 
    s1.getRange(row).clearContent()//Clear product dropdown 
    s3.getRange(1,1,array.length,1).setValues(array)//Set new customer product list in TEMP. 
    drop(row,cust)// Call drop function to build new dropdown. 
} 
if(sh=="Sheet1" && col==2){//If sheet1 is active sheet and Product (column B) is edited. 
    var cust1=e.range.offset(0,-1).getValue()//Get customer in A 
    var prod=e.range.getValue()//Get selected product 
for (var i=0;i<rng.length;i++){//Get the customer/product price 
    if(rng[i][0]==cust1){ 
     for(var j=1;j<rng[0].length-1;j++){ 
     if(rng[0][j]==prod){ 
      price=rng[i][j] 
      s1.getRange(r,5).setValue(price)//Set the price in column E 
     s1.getRange(r,2).clearDataValidations() //Remove the data validation dropdown in column B 
     } 
     }} 
}}} 
function drop(row,cust){ 
    var ss=SpreadsheetApp.getActiveSpreadsheet() 
    var s3=ss.getSheetByName("TEMP") 
    var s1=ss.getSheetByName("Sheet1") 
    var cell = s1.getRange(row);//set validation in B 
    var ocell=s1.getRange(row).offset(0, -1).getValue()//evaluate value in A 
    var cellVal=cell.getValue() 
if(ocell==cust){ 
    var lr= ss.getSheetByName("TEMP").getLastRow() 
    var range = ss.getSheetByName("TEMP").getRange(1, 1, lr, 1) 
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build();//Build the dropdown 
cell.setDataValidation(rule)}//Set the validation rules (Customers 
products) 
} 

测试表格： https://docs.google.com/spreadsheets/d/1u86sdf1_mO-Mv7hQM_hRZl3Gma-Y2lsu9ZvxSW4jA1U/edit?usp=sharing