跨工作簿设置数据有效性

跨工作簿设置数据有效性

在Excel中我们可以通过设置数据有效性更快速的输入数据，或者限制数据的输入范围。通常数据有效性的数据来源于同一个工作簿，但有时也来源于其他工作簿。

当我们使用其他工作簿的数据设置数据有效性时，我们发现Excel或者提示我们错误，或者根本无法选中其他工作簿的数据。本文就和大家分享如何跨工作簿设置数据有效性。

一、案例

如下图所示，为两个Excel工作簿文件，文件名分别为“员工信息表”和“部门列表”。

现在要求为“员工信息表”的C列“部门”设置数据有效性，其数据来源为“部门列表”中的部门。

1

如果我们按照通常设置数据有效性的操作步骤，“允许”选择“序列”，“来源”选择其他工作簿的工作表的单元格区域，如下图所示：

2

单击确定后，Excel会提示我们引用错误，如下图所示。

3

这说明设置数据有效性时，如果有效性数据来自于其他工作簿，我们不能在【来源】中使用“[工作簿名]工作表名!单元格引用”这种引用方式。

那么跨工作簿设置数据有效性的正确操作步骤是什么呢？

二、操作步骤

选中需要设置数据有效性的单元格区域，单击【数据】-【数据验证】，打开【数据验证】对话框。【允许】选择【序列】。【来源】处输入公式：

方法一、INDIRECT函数法

选中需要设置数据有效性的单元格区域，单击【数据】-【数据验证】，打开【数据验证】对话框。【允许】选择【序列】。【来源】处输入公式：

=indirect("[部门列表.xlsx]部门列表!$A$2:$A$5")

如果工作簿或工作表名称中含有空格或特殊字符，需要“[”前和“！”前使用半角单引号，即

=indirect("’[部门列表.xlsx]部门列表’!$A$2:$A$5")

4

单击确定后，即可生成数据有效性，如下图所示：

5

方法二、定义名称法

步骤1、为数据有效性数据源定义名称

打开“部门列表”工作簿，为“部门列表”工作表的A2：A5定义名称为“部门”，范围为“工作簿”。

6

步骤2、在需要设置数据有效性的工作簿定义名称

打开“员工信息”工作簿，单击【公式】-【定义名称】，打开【新建名称】对话框。在名称框中输入定义的名称（此处为“部门”），在引用位置处输入 =部门列表.xlsx!部门。注意无需为工作簿名“部门列表.xlsx”

添加“[ ]”。单击确定。

7

步骤3、设置数据有效性

选中需要设置有效性的单元格区域，单击【数据】-【数据验证】，打开【数据验证】对话框。【允许】选择“序列”，【来源】输入“=部门”

8

单击确定，C2：C8单元格区域的数据有效性就设置完成了。

三、注意事项

必须同时打开数据源工作簿和设置数据有效性的工作簿，这样才能在设置数据有效性的工作表中看到下拉菜单。

=IFERROR(VLOOKUP(A2,[M.xlsx]price!$A$2:$D$15,3,0),"")