Setting up automated "Priority" in Project Portfolio

Troy G

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")

SPIPMO

Hi @Troy G

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

Hope this helps

Thanks!

che-spi.pmo

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