Nested IF-AND-OR within the same cell...

Suresh Seetharaman

Hi All,

I am trying to automate a cell based on the status selected in 5 different cells (all within the same sheet)

For Eg.,

First 5 columns has the following Statuses.

Complete

Incomplete

In Progress

Sick

Dropout

Cancelled

Scheduled

Conditions are

Main status is 'Complete' when all 5 columns are 'Complete'

Main Status is 'Incomplete' when at least one of the columns is 'Incomplete'

Main status is 'In Progress' when at least one of the columns is 'Complete' and others are 'Scheduled'

Main Status is 'Sick' when at least on of the columns is 'Sick'

Main Status is 'Dropout' when at least one of the columns is 'Dropout'

Main Status is 'Cancelled' when at least one of the columns is 'Cancelled'

Main Status is 'Scheduled' when all 5 columns are 'Scheduled'

I would want to check all the above conditions within the same cell. I am fairly new to smart sheets and still trying to figure this out. Any help would be appreciated.

Thanks

MCorbin

What if one column is complete, one is scheduled, and one is Incomplete.... that hits several of your rules. Is there a hierarchy?

Suresh Seetharaman

Yes,

Hierarchy would be

Scheduled, In Progress, Complete, Incomplete, Sick, Dropout, Cancelled

Thanks