Nested IF(AND(OR)) Formula

Monica J

Hello! I'm trying to write a column formula that will return project task Status "Study review Complete" if the particular task is complete else should return "Study review incomplete" otherwise. 

My formula needs to read across 2 columns, and one column is a dropdown list and other is a checkbox. 

-- I want to show status -- "Study A review Complete "(If the conditions are met ) and "Study A review inComplete "(If the conditions are not met)

-- I want to show status -- "Study B review Complete "(If the conditions are met ) and "Study B review inComplete "(If the conditions are not met)

-- I want to show status -- "Study H review Complete "(If the conditions are met ) and "Study H review inComplete "(If the conditions are not met)

 

So with the above scenario in mind, I wrote a nested IF(AND(OR) formula with which i was able to set the status for "study H"(both true or false status) and "study B"(both true or false status) but can't figure out how to get the status for "study A" when the conditions are not met.

=IF(AND(columnTask@row = "Study A ", columnCheck@row = 1), "Study A review Completed " ,IF(AND(columnTask@row = "Study B", columnCheck@row = 1), "Study B Complete ", IF(OR(columnTask@row = "ABC", columnTask@row = "IEF", columnTask@row = "MD"), IF(columnCheck@row = 1, "Study H Complete", "Study H In Progress"),"Study B in Progress")))))

**** I need to include false statement status for "Study A" in the above formula if that helps ****

I've been staring at smartsheet and notepad for far too long trying to solve this, but no luck yet on that!

Any recommendations are greatly appreciated.

Mary_A

@Monica J It would help if described your use case further -- are these studies undertaken in a specific order or any order? Are you displaying these results or simply looking to see the "final" value and reporting that value on a dashboard?

The easiest way to do this if you are using a visual is not to use checkboxes but rather a single select drop-down.

Let's say these can be done in any order and that you are really checking to see if all of them are done regardless of order. By default, the column is empty, a user marks In Progress when they start the study and Complete when they are done. Three possible column values to account for:

• Blank --- equivalent to not started
• In Progress --- in the midst of studying
• Complete --- all done

Using words instead of checkboxes allows a quick visual check at any point to see where any row is in the schema. Using my example use case description, this formula does the trick:

=IF(AND(StudyA@row = "Complete", StudyB@row = "Complete", StudyC@row = "Complete"), "All Studies Complete", IF(AND(StudyA@row = "", StudyB@row = "", StudyC@row = ""), "No Studies started", "Studies in Progress"))

If you are doing order dependent, they can't start one until they complete another, you can incorporate some automation in your design. This would lock and unlock columns to enforce an order of study.