Excel-VBA协助-数据验证时间过长，需要替代 [英] Excel-VBA Assistance - Data Validation too long, need alternative

问题描述

我知道数据验证仅允许您放置255个字符.我已经命名了单元格/范围，因为我有几个要根据标准从中选择或选择某些信息的列表.我的公式如下:

I know the data validation only lets you place 255 characters. I have named cells/ranges as I have several list I'm trying to pull or select certain information from based on a criteria. My formula is as follows:

=IF(AND(RETAILER="",DISPLAY_TYPE=""),"",IF(AND(RETAILER=CODES!$F$2,(OR(DISPLAY_TYPE=CODES!$V$1,DISPLAY_TYPE=CODES!$Y$1))),CODES!$V$2:$V$49,IF(AND(RETAILER=CODE S!$F$2,(OR(DISPLAY_TYPE=CODES!$AB$1,DISPLAY_TYPE=CODES!$AE$1))),CODES!$AB$2:$AB$94,IF(AND(RETAILER=CODES!$F$3,(OR(DISPLA Y_TYPE=CODES!C1,DISPLAY_TYPE=CODES!C2,DISPLAY_TYPE=CODES!C3,DISPLAY_TYPE=CODES!C4))),"INCORRECT COMBINATION, Correct Retailer or Display Type",IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AH1),CODES!AH2:AH38,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AJ1),CO DES!AJ2:AJ10,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AL1),CODES!AL2:AL18,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=C ODES!AN1),CODES!AN2:AN18,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AP1),CODES!AP2:AP18,IF(AND(RETAILER=CODES!$F$3,DI SPLAY_TYPE=CODES!AR1),CODES!AR2:AR29,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AT1),CODES!AT2:AT29,IF(AND(RETAILER=C ODES!$F$2,(OR(DISPLAY_TYPE=CODES!C5,DISPLAY_TYPE=CODES!C6,DISPLAY_TYPE=CODES!C7,DISPLAY_TYPE=CODES!C8,DISPLAY_TYPE=CODES !C9,DISPLAY_TYPE=CODES!C10,DISPLAY_TYPE=CODES!C11))),"INCORRECT COMBINATION, Change Retailer or Display Type","")))))))))))) 

我知道，数据验证时间太长，因此，我尝试通过选择一个嵌套了公式的单元格来命名我的公式(按CTRL + F3，将其命名为CHECK_FORMULA单元格)并将上述公式放在指"框.然后，我去了C21(要进行数据验证)并输入以下内容:

I know, too long for data validation, so, I then tried to "name" my formula by choosing a cell where my formula is nested (hit CTRL+F3, named the cell CHECK_FORMULA) and placed the above formula in the "refers to" box. Then, I went to C21 (where I want to have my data validation) and entered the following:

=IF(OR(RETAILER="",DISPLAY_TYPE=""),"INVALID",CHECK_FORMULA) 

我收到的错误是列表源必须是定界列表，或者是对单个行或列的引用".该公式提供了一组零件清单，具体取决于将显示的标准集.当公式小于255个字符时，我没有这个问题，可以根据条件选择列表，没问题，但是现在，因为添加了其他列表，所以我遇到了这个问题.有没有我可以放入的宏来实现我的目标?还是可以使用CASE来获取我要完成的任务?

The error that I receive, " The list source must be a delimited list, or reference to a single row or column". The formula provides a set list of parts depending on the criteria set that will show. I didn't have this issue when the formula was under 255 characters, chose the list according to criteria, no problem, but now, because other list were added, I'm having this issue. Is there a macro I can put this into in order to accomplish my goal? or can I use CASE in a way to get what I'm trying to accomplish?

推荐答案

已解决....我将公式放在指定的范围内，并且在对Excel(Microsoft发行)应用了修复"之后，该公式起作用了:名称范围'CheckFormula'适用于以下情况:

SOLVED....I placed the formula in a named range and it worked after I applied a "fix" to my Excel (issued by Microsoft): The name range 'CheckFormula' works with the following:

  =`IF(AND(RETAILER="",DISPLAY_TYPE=""),"",IF(AND(RETAILER=CODES!$F$2,(OR(DISPLAY_TYPE=CODES!$V$1,DISPLAY_TYPE=CODES!$Y$1))),CODES!$V$2:$V$47,IF(AND(RETAILER=CODES!$F$2,(OR(DISPLAY_TYPE=CODES!$AB$1,DISPLAY_TYPE=CODES!$AE$1))),CODES!$AB$2:$AB$72,IF(AND(RETAILER=CODES!$F$3,(OR(DISPLAY_TYPE=CODES!C1048575,DISPLAY_TYPE=CODES!C1048576,DISPLAY_TYPE=CODES!C1,DISPLAY_TYPE=CODES!C2))),"INCORRECT COMBINATION, Correct Retailer or Display Type",IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AH1048575),CODES!AH1048576:AH36,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AJ1048575),CODES!AJ1048576:AJ8,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AL1048575),CODES!AL1048576:AL16,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AN1048575),CODES!AN1048576:AN16,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AP1048575),CODES!AP1048576:AP16,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AR1048575),CODES!AR1048576:AR27,IF(AND(RETAILER=CODES!$F$3,DISPLAY_TYPE=CODES!AT1048575),CODES!AT1048576:AT27,IF(AND(RETAILER=CODES!$F$2,(OR(DISPLAY_TYPE=CODES!C3,DISPLAY_TYPE=CODES!C4,DISPLAY_TYPE=CODES!C5,DISPLAY_TYPE=CODES!C6,DISPLAY_TYPE=CODES!C7,DISPLAY_TYPE=CODES!C8,DISPLAY_TYPE=CODES!C9))),"INCORRECT COMBINATION, Change Retailer or Display Type",""))))))))))))`

感谢所有提供帮助的人.

Thank you to all those who provided assistance.

这篇关于Excel-VBA协助-数据验证时间过长，需要替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！