跨两张工作表检查日期冲突的Excel条件格式问题

你遇到的提示属于Excel条件格式的限制误报——即便在同一工作簿内，直接用INDEX/MATCH组合跨工作表引用也会被判定为非法引用，以下是几个可行的解决办法：

方法1：用INDIRECT函数构建间接引用 #

把原公式修改为通过INDIRECT拼接跨工作表的列引用，避开直接引用的限制：

=COUNTIFS(INDIRECT("'Sheet 2'!"&CHAR(64+MATCH($A4,'Sheet 2'!$2:$2,0))&":"&CHAR(64+MATCH($A4,'Sheet 2'!$2:$2,0))),B$3)>0

原理：用CHAR(64+匹配列数)把列序号转成对应字母，再通过INDIRECT生成目标列的完整引用，让条件格式识别为合法引用。

方法2：添加辅助列简化引用 #

1. 在排班表工作表（Sheet1）的空白列（比如I列）输入公式，下拉填充到所有员工行：

=TEXTJOIN(",",TRUE,INDIRECT("'Sheet 2'!"&CHAR(64+MATCH($A4,'Sheet 2'!$2:$2,0))&":"&CHAR(64+MATCH($A4,'Sheet 2'!$2:$2,0))))

这个公式会把对应员工的所有休息日合并成一个字符串。
2. 条件格式公式修改为：

=ISNUMBER(SEARCH(B$3,$I4))

通过检查当前日期是否在员工休息日字符串中，触发条件格式。

方法3：使用结构化表格优化引用 #

1. 选中Sheet2的所有休息日数据（包含顶部员工姓名行），按Ctrl+T创建结构化表格，命名为RestDays。
2. 条件格式改用XLOOKUP配合TEXTJOIN：

=ISNUMBER(SEARCH(B$3,TEXTJOIN(",",TRUE,XLOOKUP($A4,RestDays[#Headers],RestDays))))

结构化表格的引用格式在条件格式中兼容性更强，可绕过跨工作表引用限制。

注意事项 #

• 若Sheet2的工作表名称有修改，需同步调整公式中的名称；
• 确保两张表中的日期格式完全一致，避免匹配失败。

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