Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

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

Options
Marie Eastman
Marie Eastman ✭✭✭
edited 12/09/19 in Archived 2017 Posts

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?

Comments

  • Kara Lumley
    Options

    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

This discussion has been closed.