Data validation (drop down list) - combining 'IF' with 'AND'

[nosense]

I am trying to combine AND and IF statement in data validation.

I have

Please Login or Register  to view this content.

and it does not work

Any suggestions?

[6StringJazzer]

Without having your workbook to test this I am guessing that the problem is that the "else" part of your IF statement is missing. If you did that in a cell, then the result would be FALSE if your condition were not true. But it may be invalid to omit it in a data validation list.

Please explain what "doesn't work" means.

• Does it do nothing?
• Does it produce error messages? If so, what do the messages say?
• Does it produce unexpected/wrong results? If so, what do you expect, and what do you actually get?

[nosense]

Without having your workbook to test this I am guessing that the problem is that the "else" part of your IF statement is missing. If you did that in a cell, then the result would be FALSE if your condition were not true. But it may be invalid to omit it in a data validation list.

Please explain what "doesn't work" means.

• Does it do nothing?
• Does it produce error messages? If so, what do the messages say?
• Does it produce unexpected/wrong results? If so, what do you expect, and what do you actually get?

Thanks for the reply. There is error msg prompted saying: "The list source must be a deimited list, or a reference to single row or column."

I have 4 drop down lists and source data for lists in different sheets in the workbook. Depending on value i select i want other lists to show me values from selected cells (like you could figure out from the code i posted in my thread).
Thanks

[quekbc]

You need to assign a value_if_false parameter on this. Choose an empty cell, make sure it's empty (or have it say "N/A"), and then add a reference to that cell.

Please Login or Register  to view this content.

where A1 in 'SomeHiddenSheet' is a blank cell.

[shukla.ankur281190]

Once share your workbook to us with expected result.

[nosense]

You need to assign a value_if_false parameter on this. Choose an empty cell, make sure it's empty (or have it say "N/A"), and then add a reference to that cell.

Please Login or Register  to view this content.

where A1 in 'SomeHiddenSheet' is a blank cell.

Thanks for this. It did work but unfortunately there is another issue when I add another "IF" statement. The message says "You may not use reference operatos (such as unions, intersections, and ranges) or array constants for Data Validation criteria"

Please Login or Register  to view this content.

[JohnTopley]

Your IF statement "returns" an array if TRUE so I think this the problem: what are you trying to do?

and is this what you want (apart from the array issue) ...combining the IFs

=IF(AND($D$12="Mk6e-i2",$F$12="Unmachined",$H$12="CA"),'Mk6e-i2 CA'!$F$9:$G$200,IF(AND($D$12="Mk6e-i2",$F$12="Machined",$H$12="CA"),'Mk6e-i2 CA Machined'!$F$9:$G$200,$A$1))

[nosense]

Your IF statement "returns" an array if TRUE so I think this the problem: what are you trying to do?

and is this what you want (apart from the array issue) ...combining the IFs

=IF(AND($D$12="Mk6e-i2",$F$12="Unmachined",$H$12="CA"),'Mk6e-i2 CA'!$F$9:$G$200,IF(AND($D$12="Mk6e-i2",$F$12="Machined",$H$12="CA"),'Mk6e-i2 CA Machined'!$F$9:$G$200,$A$1))

Yes, I need the function to return an array (in Data Validation option)
There is a message when i put your suggestion in, saying " "The list source must be a deimited list, or a reference to single row or column."

[quekbc]

Ah, didn't notice that the first time. The error says exactly what it is.

The list source must be a delimited list, or a reference to single row or column.

The references $F$9:$G$200 is 191 rows and 2 columns (i.e. does not fit the criteria above)

[nosense]

Ah, didn't notice that the first time. The error says exactly what it is.

The list source must be a delimited list, or a reference to single row or column.

The references $F$9:$G$200 is 191 rows and 2 columns (i.e. does not fit the criteria above)

Nice one
It does work,

Rep for You