AUTOMATE PROGRESS BAR BY MULTIPLE CONDITIONS

Hello, I am trying to automate my progress bar, basing it's progress on multiple conditions.

I have 10 columns I would like the progress bar to consider.


Example below of how i would like it to work:

If Cost 1 AND Hours 1 IS NOT Empty then progress = 25%

If Parts AND Survey AND time IS NOT Empty then progress = 50%

If Qualified AND Submit AND CBA IS NOT Empty then progress = 75%

If Case AND Log IS NOT Empty then progress = 100%


Please can I ask for some support on how I would go about writing this formula. I am used to Alteryx and Excel formulas but completely stumped on Smartsheet. Any help or advice would be hugely appreciated.

Equally if there is another way i can go about showing this progress in a more detailed way ie 10%,20%30% etc then i would welcome the suggestions!



Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    This would be a nested IF with AND functions for the arguments...

    =IF(AND(Case@row <> "", Log@row <> ""), "Full", IF(AND(Qualified@row <> "", Submit@row <> "", CBA@row <> ""), "Three Quarter", IF(AND(Parts@row <> "", Survey@row <> "", Time@row <> ""), "HHalf", IF(AND([Cost 1]@row <> "", [Hours 1]@row <> ""), "Quarter", "Empty"))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!