I need help with fixing my Smartsheet Formula to pick up the highest level of Revision Status.
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
-
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
-
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!
-
Hi @Dan Palenchar, thank you for your quick help. Much appreciated! Your formula worked perfectly!!
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!