Need to refine this code....

[Avogadro]

I have to repeat this code for oknProductID_1 through oknProductID_20. As you can see it is already to long for one ProductID.

Any help out there?

=IF(AND(oknProductID_1="MW 600",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 102",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 101",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 100",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 888",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 884",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 871",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 10",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 11",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 12",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 14",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 500",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 802",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="BEM 802",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="Mi 150",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MI 151",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="Mi 605",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="pH 600",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MC 122",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MC 125",NOT(oknShipCountry="USA")),AX34,""))))))))))))))))))))

IF(AND(oknProductID_1="MW 600",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 102",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 101",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 100",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 888",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 884",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MA 871",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 10",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 11",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 12",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 14",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 500",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MW 802",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="BEM 802",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="Mi 150",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MI 151",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="Mi 605",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="pH 600",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MC 122",NOT(oknShipCountry="USA")),AX34,IF(AND(oknProductID_1="MC 125",NOT(oknShipCountry="USA")),AX34,""))))))))))))))))))))

 

[shg]

That one can be simplified to

=IF(AND(oknShipCountry<>"USA",
OR(oknProductID_1={"MW 600","MW 102","MW 101","MW 100","MA 888","MA 884","MA 871","MW 10","MW 11","MW 12","MW 14","MW 500","MW 802","BEM 802","Mi 150","MI 151","Mi 605","pH 600","MC 122","MC 125"}), ), AX34, "")

Then you can put all those values in a range (say A1:A20), and use

=IF(AND(oknShipCountry <> "USA", OR(oknProductID_1 = A1:A20)), AX34, "")

The latter formula MUST be confirmed with Ctrl + Shift + Enter instead of just Enter.

 

[Avogadro]

Thanks, I'll work on that.

 

[shg]

You're welcome.