Once a field is marked to yes ignore the rest of the formula

Marie Eastman

I am trying to create a sheet where as a step is completed the steps are updated with a checkmark symbol. As a drop down comment is added in a column the formula updates the step to complete. However onece I change the drop down comment the symbol changes back to "No" or X. I would like to know if there is a why to write into the formaula, if this was ever Yes - keep as yes and ignore the rest of the formula. I tried using the column that was in the formula and was getting a cicular error. I have found a workaround in a sense I can add criteria to my formula that as all susequent steps are choosen to also update this field to "Yes" or checked. 

 

Example. 

Orginial Formula:

=IF(Notes1 = "AM", "Yes", IF(Notes1 = "PM", "Yes", IF(Notes1 = "Attempt Input", "Hold", "No")))))

 

Cicular error:

=IF(([Input Complete]1) = "Yes", "Yes", IF(Notes1 = "AM", "Yes", IF(Notes1 = "PM", "Yes", IF(Notes1 = "Attempt Input", "Hold", "No"))))

 

Workaround:

=IF(Notes1 = "AM", "Yes", IF(Notes1 = "PM", "Yes", IF(Notes1 = "To Audit", "Yes", IF(Notes1 = "Audit Pass", "Yes", IF(Notes1 = "Attempt Input", "Hold", "No")))))

 

Any ideas?

Kara Lumley

Hi Marie,

I see what you are saying - the circular error is because you cannot reference the same cell that the formula is in, in the formula. You would actually need a third column to check for the "Yes" in Input Complete column if that was what you wanted to check for first.

Feel free to email ge-team@smartsheet.com if you need further help.

Thank you,

Kara