Best formula for a complicated desired result?

Nick Amsler

Hello,

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?

Paul Newcome

Give this a try:

=IFERROR(IF(COUNT(CHILDREN([Shift Number]@row)) <> 0, IF(COUNTIFS(CHILDREN([Employee Title]@row), CONTAINS("FTO", @cell))> 0, "Red", [Shift Status]@row)), [Shift Status]@row)

Paul Newcome

Try something like this:

=IF(COUNT(CHILDREN([Shift Number]@row)) <> 0, IF(COUNTIFS(CHILDREN([Employee Title]@row), @cell = "FTO")> 0, "Red", [Shift Status]@row))

Hollie Green

Can you share your current If formula that is giving the error? It may just need an IfError to account for the #No Match and blanks in the Employee Title column

Nick Amsler

@Paul Newcome

That came very close! closer than I have gotten before. I can't thank you enough! I am now just getting "NO MATCH" on those that have the red or yellow status. Also, is there anyway to make it if the "FTO" is found anywhere in those respective children rows versus exact as I have other titles that include "FTO" like "LEAD FTO" that would need the same result.

Which honestly this is fine because I simply need green on the ones that are good to go and care less on the ones that don't meet the criteria.

Paul Newcome

Give this a try:

=IFERROR(IF(COUNT(CHILDREN([Shift Number]@row)) <> 0, IF(COUNTIFS(CHILDREN([Employee Title]@row), CONTAINS("FTO", @cell))> 0, "Red", [Shift Status]@row)), [Shift Status]@row)

Nick Amsler

@Paul Newcome

That worked, I am always amazed at how these formulas come together and how it seems easy for others to get to. I literally spent hours trying to piece this together before submitting the question. I can't thank you enough! Now if I can just get SmartSheet to incorporate time data into their available properties.

Thank you again and have a great holiday season!

Paul Newcome

Happy to help. 👍️

My main specialty in Smartsheet is formulas. I am pretty good at quite a few other things in here, but I'm a total formula nerd and have been for about 5 years now (many more in Excel). I am one of the weird ones that does this for fun (no judging). Hahaha

As for time... I have been seeing little hints here and there for the past couple of years, so I am keeping my fingers crossed that it is coming soon. In the meantime...

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time