Setting up automated "Priority" in Project Portfolio

My company is moving to Smartsheet and we would like to have our "Priority" column automatically populate as Strategic, High, Medium, Low, or Not Prioritized depending on criteria in 2 separate columns (Impact and Urgency).

If Impact and Urgency = "High", Priority = "Strategic

If Impact = "High" and Urgency = "Medium", Priority = "High" or if Impact = "Medium" and Urgency = "High", Priority = "High"

If Impact = "Medium" and Urgency = "Medium". Priority = "Medium" or if Impact = "Medium" and Urgency = "Low". Priority = "Medium", or if Impact = "Low" and Urgency = "Medium". Priority = "Medium"

If Impact = "Low" and Urgency = "Low", Priority = "Low"

IF Impact = "Not Prioritized" and Urgency = "Not Prioritized", Priority = "Not Prioritized"

Formula so far that works:

=IF(AND(Impact@row = "High", Urgency@row = "High"), "Strategic")

Formula so far that is not working:

=IF(AND(Impact@row = "High", Urgency@row = "High"), "Strategic"), IF(AND(Impact@row = "High", Urgency@row = "Medium"), "High"), IF(AND(Impact@row = "Midium", Urgency@row = "High"), "High"), IF(AND(Impact@row = "Medium", Urgency@row = "Medium"), "Medium")

Answers

  • SPIPMO
    SPIPMO ✭✭✭✭

    Hi @Troy G

    closing brackets (for IF) should appear at the end of expression

    Hope this helps

    Thanks!

    che-spi.pmo

  • Troy G
    Troy G ✭✭

    @SPIPMO

    Thanks, this worked with having all the brackets at the end. The final working solution for those needing something similar is below.

    =IF(AND(Impact@row = "High", Urgency@row = "High"), "Strategic", IF(AND(Impact@row = "High", Urgency@row = "Medium"), "High", IF(AND(Impact@row = "Medium", Urgency@row = "High"), "High", IF(AND(Impact@row = "Medium", Urgency@row = "Medium"), "Medium", IF(AND(Impact@row = "Medium", Urgency@row = "Low"), "Medium", IF(AND(Impact@row = "Low", Urgency@row = "Medium"), "Medium", IF(AND(Impact@row = "Low", Urgency@row = "Low"), "Low", IF(AND(Impact@row = "Not Prioritized", Urgency@row = "High"), "Not Prioritized", IF(AND(Impact@row = "Not Prioritized", Urgency@row = "Medium"), "Not Prioritized", IF(AND(Impact@row = "Not Prioritized", Urgency@row = "Low"), "Not Prioritized", IF(AND(Impact@row = "Not Prioritized", Urgency@row = "Not Prioritized"), "Not Prioritized", IF(AND(Impact@row = "High", Urgency@row = "Not Prioritized"), "Not Prioritized", IF(AND(Impact@row = "Medium", Urgency@row = "Not Prioritized"), "Not Prioritized", IF(AND(Impact@row = "Low", Urgency@row = "Not Prioritized"), "Not Prioritized", IF(AND(Impact@row = "Low", Urgency@row = "High"), "Medium", IF(AND(Impact@row = "High", Urgency@row = "Low"), "Medium", IF(AND(Impact@row = "", Urgency@row = ""), "Not Prioritized")))))))))))))))))


    Thanks,

    Troy G - PMP

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!