Skip to main content

Hello-



Looking for a formula to where if I have multiple fields with single select options to act as an IF statement that overrides the last stage that is selected:



So when any of these are selected based on the rules set in the formula, only that stage will show regardless if project status is approved for graphic design and the formula displays DESIGN STAGE, once Graphic Design Status is not blank, it will show the status for that stage - make sense?



IF({PROJECT STATUS}=BLANK(),‘’,


IF({PROJECT STATUS} = ‘APPROVED FOR GRAPHIC DESIGN’,‘DESIGN STAGE’,



IF({GRAPHIC DESIGN STATUS}=BLANK(),‘’,


IF({GRAPHIC DESIGN STATUS} = ‘READY FOR REVIEW’,‘DESIGN STAGE’,


IF({GRAPHIC DESIGN STATUS} = ‘REQUIRTES REVISION’,‘DESIGN STAGE’,


IF({GRAPHIC DESIGN STATUS} = ‘ON HOLD’,‘DESIGN STAGE’,


IF({GRAPHIC DESIGN STATUS} = ‘APPROVED FOR SUBMITTAL’,‘SUBMITTAL STAGE’,



IF({SUBMITTAL STATUS}=BLANK(),‘’,


IF({SUBMITTAL STATUS} = ‘IN SUBMITTAL’,‘SUBMITTAL STAGE’,


IF({SUBMITTAL STATUS} = ‘READY FOR REVIEW’,‘SUBMITTAL STAGE’,


IF({SUBMITTAL STATUS} = ‘REQUIRES REVISION’,‘SUBMITTAL STAGE’,


IF({SUBMITTAL STATUS} = ‘ON HOLD’,‘SUBMITTAL STAGE’,


IF({SUBMITTAL STATUS} = ‘APPROVED FOR FINAL PROOFING’,‘FINAL PROOFING STAGE’



))))))))))

This is a prime example where nested SWITCH statements would be better.



Since SWITCH statements let you check the value of a field against several predefined options, and allows for a “default” value when none of the supplied values are found, you can write a more streamlined formula than using IFs. Since the name of the stage is supposed to be the value for all but 2 instances (blank or the last option in the list), the name of the stage is the default.



Also, since Submittal overrides Graphic Design, which overrides Project, your logic should work in reverse: check for values in those fields the other way around. For that reason, the blank condition outputs another SWITCH to check the previous stage’s value.



SWITCH(


  {SUBMITTAL STATUS},


  'APPROVED FOR FINAL PROOFING', 'FINAL PROOFING STAGE',


  '', SWITCH(


    {GRAPHIC DESIGN STATUS},


    'APPROVED FOR SUBMITTAL', 'SUBMITTAL STAGE',


    '', SWITCH(


      {PROJECT STATUS},


      'APPROVED FOR GRAPHIC DESIGN', 'DESIGN STAGE',


      ''


    ),


    'DESIGN STAGE'


  ),


  'SUBMITTAL STAGE'


)

Reply


Terms & Conditions