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

Haissam

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:







SoS | Dan Palenchar

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

SoS | Dan Palenchar

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

Haissam

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

SoS | Dan Palenchar

@Haissam glad it worked!!