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

Find more posts tagged with

Accepted answers

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.