Best formula for a complicated desired result?



I have tried many different formulas and even tried combining formulas but can't seem to get one that works in a way for syntax to be used as a column formula. Any help would be appreciated, and I thank anyone in advance for tackling this.

The desire result is a formula that can be converted into a column formula for the following:

if the shift status is red, then the last column should show red (note I am using parent rows) and same if the status is yellow then same for the last column.

But if the employee title contains "FTO", then the last column should be red as well even though the shift status is green. I can get close with "IF" formulas but once I try to make it a column formula it states there is a syntax error. I am at loss for what to do. I have many hundreds of these that need to be constantly updated so manually upkeeping this is just out of the question.

Again, any help would be greatly appreciated.

Should I just stop using formulas for this issue and instead use conditional formatting?


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!