在论坛中，有朋友问起——数据有效性(不能重复输入)的跨表运用：
http://www.excelba.com/bbs/bbs_detail.asp?bid=1&aid=931
现提供三种方法，以下说明请参考底下的实例：
1、全部表的A列不能重复输入
2、工作表第3~第6个表的B列不能重复输入
3、工作表名为Sheet1~sheet6的工作表的C列不能重复输入
在插入/名称/定义中,有如下名称定义:
| 名称 | 内 容 | 定义方法 | 用途 |
| AL | =SUMPRODUCT(COUNTIF(INDIRECT("’"&sheetall&"’!A:A"),Sheet5!$A1)) | 选择A1格,再键入内容 | 对所有表A列计数 |
| BL | =SUMPRODUCT(COUNTIF(INDIRECT("’"&sheetlist&"’!B:B"),Sheet5!$B1)) | 选择B1格,再键入内容 | 对第3~第6个表B列计数 |
| CL | =SUMPRODUCT(COUNTIF(INDIRECT("Sheet"&ROW(Sheet5!$1:$6)&"!C:C"),Sheet5!$C11)) | 选择C1格,再键入内容 | 对Sheet1~sheet6C列计数 |
| sheet | =REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),)&T(NOW()) | 随意单元格,再键入内容 | 提取工作表名称(单个) |
| sheetall | =LOOKUP(ROW(INDIRECT("1:"&COLUMNS(sheet))),MATCH(sheet,sheet,),sheet) | 随意单元格,再键入内容 | 提取全部工作表名称(数组) |
| sheetlist | =LOOKUP(ROW(Sheet5!$3:$6),MATCH(sheet,sheet,),sheet) | 随意单元格,再键入内容 | 提取第3~第6工作表名称(数组) |
数据有效性的定义方法:
| 位置 | 方 法 | 公式 |
| A列 | 选择A列,在数据/有效性中,取消忽略空值(重要),在允许中选择公式,再键入公式 | =OR(A1="",AL=1) |
| B列 | 选择B列,在数据/有效性中,取消忽略空值(重要),在允许中选择公式,再键入公式 | =OR(B1="",BL=1) |
| C列 | 选择C列,在数据/有效性中,取消忽略空值(重要),在允许中选择公式,再键入公式 | =OR(C1="",CL=1) |
参考资料:
数据有效性(不能重复输入)的跨表运用(officefans.net/作者:yigepure): 点击浏览
利用公式提取工作表名称请参考: 点击浏览
其它数据有效性的应用(三种)请参考: 点击浏览:
实例下例:点击下载