Google Sheets多条件配置data validation dropdown筛选合格培训师

现有表结构约定 #

配置基于现有两张工作表完成，无需额外按财年拆分新建表，全量历史记录可直接留存：

• Trainers 工作表：存储所有培训师全周期档案，包含培训师姓名、复训完成日期、对应财年（FY）、IRT专项资质字段，其中资质列值为TRUE即代表培训师具备IRT授课资质
• Training Participants 工作表：用于录入培训参与记录，其中固定一列为培训举办日期，一列为培训类型，需要给培训师录入列添加动态下拉选择框

筛选规则 #

下拉列表需自动匹配当前录入行的信息做两层筛选：

• 基础匹配：仅展示当前行培训日期所属财年下、已完成当年复训的培训师
• 特殊匹配：如果当前行录入的培训类型为IRT，额外叠加筛选该财年下IRT资质标记为TRUE的培训师

规则匹配参考：

• 录入2022年5月15日开展的IST培训记录时，财年判定为FY21/22，下拉列表展示该财年所有合格培训师：Amy Peterson、Stan Roberts、Josh Smith
• 录入2022年7月1日开展的IRT培训记录时，财年判定为FY22/23，下拉列表仅展示该财年具备IRT资质的培训师：Amy Peterson、Gabe Winters

配置步骤 #

1. 打开Training Participants工作表，选中「培训师」列需要做录入限制的所有数据行（注意不要选中表头行，假设首个数据行为第2行）
2. 点击顶部菜单栏「数据」-「数据验证」，验证条件选择「下拉列表（来自范围）」，在范围输入框中填入以下动态筛选公式：

=FILTER(
  Trainers!A:A,
  Trainers!C:C=IF(MONTH(E2)>=7,"FY "&YEAR(E2)&"/"&RIGHT(YEAR(E2)+1,2),"FY "&YEAR(E2)-1&"/"&RIGHT(YEAR(E2),2)),
  IF(D2="IRT",Trainers!E:E=TRUE,1=1)
)

3. 根据自身表的实际列位置，替换公式中对应的列引用：
   • Trainers!A:A：替换为Trainers表存储培训师姓名的列
   • Trainers!C:C：替换为Trainers表存储财年（FY）标记的列
   • Trainers!E:E：替换为Trainers表存储IRT资质布尔值的列
   • E2：替换为Training Participants表存储培训举办日期列的首个数据行单元格
   • D2：替换为Training Participants表存储培训类型列的首个数据行单元格
4. 根据需要设置无效输入时的提示规则，点击「完成」即可生效

注意事项 #

• 公式中引用当前行单元格（E2、D2）时不要加$锁定行号，保证逐行验证时公式能自动匹配当前行的日期和培训类型
• 后续新财年的培训师复训记录直接追加到Trainers表末尾即可，下拉列表会自动同步更新，不需要手动调整数据验证的范围
• 全量历史财年的培训师记录可永久保留在Trainers表中，不会干扰其他财年的下拉选项展示

内容的提问来源于stack exchange，提问作者Bri