Data validation Multiple IF statements (with ANDs) referenced to different sheets

[nosense]

Please Login or Register  to view this content.

I am gtting this error message "You may not use reference operatos (such as unions, intersections, and ranges) or array constants for Data Validation criteria"

[berlan]

Would this one work?

=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]

Would this one work?

=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))

It wont allow this
"The list source must be a deimited list, or a reference to single row or column."

[Richard Buttrey]

Hi,

Probably because you're not returning a single column list, but please upload the workbook so that we can see in context.

[TMS]

The first problem is that the references are for F9:G200 in both cases. It needs to be a single column reference, so maybe F9:F200.

Second problem is that it probably won't allow you to refer to another sheet for the criteria. To get around that, you'd have to define Name Ranges to refer to the range on each of the sheets.

Not sure if you'll be able to get it to work but, if you can, there may be a performance issue if you extend this over a long list of data entry cells.

As Richard says, can we see the workbook?


Regards, TMS

[duanzhuanming]

Try this:
=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]

Hi,

Probably because you're not returning a single column list, but please upload the workbook so that we can see in context.

Yes, You are all right,

Thanks you very much!

[Richard Buttrey]

Glad to have helped and thanks for the rep.