Microvellum Community

Formula Driven Material - Formula HELP

We are working on setting up our foundation library using formula driven materials, and are running into the issue of how to come up with and name each possible material combination.  We have projects where we can have 10 different laminate colors, and 3 different Interior Melamine Colors which makes the combination of PL1 through PL10 and MEL1 through MEL3 very difficult to setup.

We came up with the following formula, but it is really long and when copied into MV it say invalid and I am assuming this is due to its length.  Is there a way we are missing to simplify this formula?

=IF(Selected_PL="PL-1", PL_1_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_1 & IF(PL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-2", PL_2_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_2 & IF(PL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-3", PL_3_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_3 & IF(PL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-4", PL_4_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_4 & IF(PL_4_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-5", PL_5_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_5 & IF(PL_5_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-6", PL_6_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_6 & IF(PL_6_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-7", PL_7_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_7 & IF(PL_7_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-8", PL_8_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_8 & IF(PL_8_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-9", PL_9_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_9 & IF(PL_9_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_PL="PL-10", PL_10_Core & IF(Formaldehyde_Free_NAUF_or_ULEF=1, " NAUF", "") & " - " & MelSelect & IF(Selected_MEL="Mel-1", IF(MEL_1_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(Selected_MEL="Mel-2", IF(MEL_2_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), IF(MEL_3_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"))) & " LINER UP - " & _PL_10 & IF(PL_10_Has_Grain_Direction=1, " [GRAIN]", " [SOLID]"), "")))))))))))


Basically we are trying to cover each possible exterior and interior color combination as possible with one Formula.  However we are open to ideas on better ways to do this.

    Toolbox BSB Survey



    Have you been using Toolbox BSB? We'd love to hear what you think!

    Take the Survey

      MVU eLearning



      Grow Your Knowledge
      Follow along with RJ as he takes you on a journey to build your foundational knowledge of Toolbox.


        Follow us on:

                 

          ERP for Millwork Shops


          Discover how Microvellum and INNERGY streamline operations for cabinet shops and millwork manufacturers.