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

我觉得自己有点傻，无法弄清楚这一点。这是我设置的数据验证：

单元格范围：Journal!J2

标准：从范围中列出 - Journal!W2:X2

细胞范围：Journal!M2

标准：从范围中列出 - Journal!Y2:AA2

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

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

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

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

定制配方

基于自定义公式的验证

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

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

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

Script

可以使用一个应用程序脚本 https://www.google.com/script/start/管理数据验证规则。以下脚本在 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);
  }
}