validation - 在 Google 电子表格中复制/粘贴数据验证

Question

我觉得有点傻，无法弄清楚这一点。所以这是我设置的数据验证:

单元格范围:Journal!J2

标准:范围内的列表 - Journal!W2:X2

单元格范围:Journal!M2

标准:范围内的列表 - Journal!Y2:AA2

这在我的第一排很棒。我创建了另一行，我希望它将所有“2”更新为“3”。单元格范围正确更新，但条件没有正确更新，除了手动进入并更新它之外，我想不出一个简单的解决方案。

我试过复制/粘贴以及粘贴特殊 -> 数据验证。

我知道像 $Y$2 这样的东西会修复行/列，但这与我想要的相反。我想我想保持相对公式而不是绝对公式？

Answer 1

实际上，“范围内的列表”类型的验证将对列表的引用视为绝对而不是相对。我知道两种解决方法:

自定义公式

基于自定义公式的验证

=not(isna(match(J2, W2:X2, 0)))

相当于“值必须来自范围 W2:X2”，它将被正确复制，对 W2:X2 的引用是相对的。

缺点:您没有获得带有自定义公式验证的单元格内下拉列表。

脚本

可以使用 Apps Script管理数据验证规则。以下脚本在范围 J2:J100 的每个单元格中设置数据验证规则，其中值必须来自同一行的 W:X。

function validate() {    
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("J2:J100");
  var valuesColumn = 23;  // begins in W 
  var valuesLength = 2;   // has length 2, so W:X
  var firstRow = range.getRow();
  for (var i = 0; i < range.getHeight(); i++) {
    var rule = SpreadsheetApp.newDataValidation()
               .requireValueInRange(sheet.getRange(firstRow + i, valuesColumn, 1, valuesLength), true)
               .setAllowInvalid(false)
               .build();
    range.offset(i, 0, 1, 1).setDataValidation(rule);
  }
}