Excel数据验证:如果B行中的单元格为空，则列出A行 [英] Excel Data Validation : List row A IF cells in rows in B are empty

问题描述

我正在尝试创建一个数据验证列表，该列表仅在该行旁边的单元格为空时显示该行中的单元格.在下面，您可以找到带有我要实现的结果的封闭图像.

I am trying to create a data validation list that only shows the cells in the row if the cell next to it is empty. Below you can find an enclosed image with the result I would like to achieve.

所以类似于数据验证列表:B行中的A行IF单元为空

So something like data validation list: row A IF cells in rows in B are empty

预先感谢您的帮助.

推荐答案

在C2中放入=IF(B2="no","",A2)并将其拖动到C5.

In C2 put =IF(B2="no","",A2) and drag it to C5.

然后选择单元格列表"选项中的单元格C2:C5，以进行数据验证.

Then select the cell C2:C5 for the select from list option for the data validation.

希望有帮助.

[编辑以适合清洁..

做:

D1  --->  =COUNTA(D2:D5)-COUNTBLANK(D2:D5)          
D2  --->  =IF(B2="no","",A2)
E2  --->  =IF(D2="",E1,E1+1)
F2  --->  =F1+1
G2  --->  =IFERROR(INDEX(D:D,MATCH(F2,E:E,0)),"")

并将D2:G2拖动到G5.

..放在:

=IF(D1=1,$G$2,IF(D1=2,$G$2:$G$3,IF(D1=3,$G$2:$G$4,IF(D1=4,$G$2:$G$5))))

我认为这样可以清理干净.

I think that will do the clean up.

这篇关于Excel数据验证:如果B行中的单元格为空，则列出A行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！