I need help with fixing my Smartsheet Formula to pick up the highest level of Revision Status.

Options

Hello everyone,
I need your help with fixing my Smartsheet formula. I am preparing a Document Control Sheet inside the Smartsheet, I have 4 columns titles as (Rev.0 Status, Rev.1 Status, Rev.2 Status, Rev.3 Status) and in these columns I will write the revision stage status of project documents (order from lowest to highest: AFP < AFD < AFC < As Built)

Now I want to create another column, which will check all these four column of Revisions and pick the highest value mentioned against any revision of document mentioned, that is AFP < AFD < AFC < As Built.

I am trying this kind of formula but with no succes. Would really appreciate if anyone can share a work around or fix formula please. Thank you!

=MAX(IF([Rev.0 Status]1 = "As Built", 4, IF([Rev.0 Status]1 = "AFC", 3, IF([Rev.0 Status]1 = "AFD", 2, IF([Rev.0 Status]1 = "AFP", 1, 0))))),IF([Rev.1 Status]1 = "As Built", 4, IF([Rev.1 Status]1 = "AFC", 3, IF([Rev.1 Status]1 = "AFD", 2, IF([Rev.1 Status]1 = "AFP", 1, 0))))),IF([Rev.2 Status]1 = "As Built", 4, IF([Rev.2 Status]1 = "AFC", 3, IF([Rev.2 Status]1 = "AFD", 2, IF([Rev.2 Status]1 = "AFP", 1, 0)))),IF([Rev.3 Status]1 = "As Built", 4, IF([Rev.3 Status]1 = "AFC", 3, IF([Rev.3 Status]1 = "AFD", 2, IF([Rev.3 Status]1 = "AFP", 1, 0)))))

Sample of how my sheet look like:





Best Answer

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Haissam

    You're on the right track using an IF() statement, but MAX() is used when you are evaluating dates or numbers, not text values. Try this:

    =IF(CONTAINS("AFP", [Rev.0]@row:[Rev.3 Status]@row), "AFP", IF(CONTAINS("AFD", [Rev.0]@row:[Rev.3 Status]@row), "AFD", IF(CONTAINS("AFC", [Rev.0]@row:[Rev.3 Status]@row), "AFC", IF(CONTAINS("As Built", [Rev.0]@row:[Rev.3 Status]@row), "As Built"))

    CONTAINS() looks through all of your status columns (and technically the date columns, but that doesn't really matter) for a value and than returns that value if it is found. The series of IF() statements moves along in the order of priority you specific, so once a value in the chain is found it will return that and stop looking

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Answers

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Answer ✓
    Options

    Hello @Haissam

    You're on the right track using an IF() statement, but MAX() is used when you are evaluating dates or numbers, not text values. Try this:

    =IF(CONTAINS("AFP", [Rev.0]@row:[Rev.3 Status]@row), "AFP", IF(CONTAINS("AFD", [Rev.0]@row:[Rev.3 Status]@row), "AFD", IF(CONTAINS("AFC", [Rev.0]@row:[Rev.3 Status]@row), "AFC", IF(CONTAINS("As Built", [Rev.0]@row:[Rev.3 Status]@row), "As Built"))

    CONTAINS() looks through all of your status columns (and technically the date columns, but that doesn't really matter) for a value and than returns that value if it is found. The series of IF() statements moves along in the order of priority you specific, so once a value in the chain is found it will return that and stop looking

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

  • Haissam
    Haissam ✭✭✭
    Options

    Hi @Dan Palenchar, thank you for your quick help. Much appreciated! Your formula worked perfectly!!

  • SoS | Dan Palenchar
    SoS | Dan Palenchar ✭✭✭✭✭✭
    Options

    @Haissam glad it worked!!

    School of Sheets (Smartsheet Partner)

    If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!