Formula for Status signs based on multiple columns

Formula for Status signs based on multiple columns

Current Formula is working based on column "Scott Tomren Approval":

=IF([Scott Tomren approval]1 =

"Submitted", "Hold", IF([Scott Tomren approval]1 =

"Approved", "Go", IF([Scott Tomren approval]1 =

"Declined", "No")))

Which returns the following symbols:

I need to have the same symbols returned in this same column for 3 more columns:

Steve Jordan approvals

Jonah Firestone approvals

Approval Status

but cannot figure out the OR statement


Thank you.

Answers

  • If any of Scott Tomren or Steve Jordan can approve,the formula would look like this :

    =IF(OR([Scott Tomren approval]1 = "Submitted", [Steve Jordan approvals]1 = "Submitted"), "Hold", IF(OR([Scott Tomren approval]1 = "Approved", [Steve Jordan approvals]1 = "Approved"), "Go", IF(OR([Scott Tomren approval]1 = "Declined", [Steve Jordan approvals]1 = "Declined"), "No")))

    See also https://help.smartsheet.com/function/or

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Shirley Pruitt

    Does @Etienne Mermillod's solution work for you? If not, can you write out all of the logic you would like to incorporate for each status. An example would be


    "Yes" = if all 3 are "Approved".

    "Hold" = if there are any still "Submitted".

    "No" = if any are "Declined".

  • Thank you for your feedback - I am not exactly sure what you mean by the above examples. How would I enter them into the formula?


    I tried the formula provided by @Etienne Mermillod, but it says "#unparseable".

  • @Etienne Mermillod, I tried the formula, but it says "#unparseable". Not sure what it means, except it is not working.


    Thank you for the help!

  • @Paul Newcome


    Yes, I believe your example is what I am after. The Approval will only be sent to one of the Approvers for each entry. However, in order to condense the symbols, I would like them to appear in the one box if any of the approvers listed in any of the columns are notified, and updated as they have been approved or denied.


    Thank you

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    @Shirley Pruitt I didn't mean for you to enter them into a formula. I meant for you to spell out exactly what you want your formula to do for all possible scenarios in a more sentence like structure. Once we establish what you want to happen, then we can start working out the formula itself.


    Basically plan it out in plain English so-to-speak then translate into a formula.

  • @Paul Newcome


    Thank you - that is exactly what my son said when I asked him what you meant.

    What I want it to do is to have the appropriate symbol appear in the one column, regardless of which of the approvers has been designated, based on the workflow. I have the formula for having the symbols updated based on one approver's response (approve or deny), but cannot figure out how to add in having the symbols show in the same column when any of the other approvers respond.

    Thanks again!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    It really depends on how you want to determine things.

    Let's try this. I will give some scenarios and you give me the symbol you want returned.

    All 3 Are Submitted?

    2 Are Submitted and 1 is Approved?

    2 Are Submitted and 1 is Declined?

    1 is Submitted and 2 are Approved?

    1 is Submitted and 2 are Declined?

    1 is Submitted, 1 is Approved, and 1 is Declined?

    2 are Approved and 1 is Declined?

    2 are Declined and 1 is Approved?

    All 3 are Approved?

    All 3 are Declined?


    Does that make sense?

  • @Paul Newcome

    So sorry to not be explaining this correctly - none of the above scenarios will ever occur, as I only need to track one instance or reply for any given row.

    My automated workflows are issue to one of the four approvers based on the location selected from the form that has been submitted. There will never be a time when more than one approver will be approving the same location.

    I need the response from the one approver (from four possible approvers) to display the "Hold", "Go", "No" symbol for the row.

    I am trying to combine these 5 formulas as an "OR" statement so that I do not have to have 5 different Approval Symbols columns.

    =IF([Scott Tomren approval]1 =

    "Submitted", "Hold", IF([Scott Tomren approval]1 =

    "Approved", "Go", IF([Scott Tomren approval]1 =

    "Declined", "No")))

    OR

    =IF([Steve Jordan approval]1 =

    "Submitted", "Hold", IF([Steve Jordan approval]1 =

    "Approved", "Go", IF([Steve Jordan approval]1 =

    "Declined", "No")))

    OR

    =IF([RJ Aubert approval]1 =

    "Submitted", "Hold", IF([RJ Aubert approval]1 =

    "Approved", "Go", IF([RJ Aubert approval]1 =

    "Declined", "No")))

    OR

    =IF([Jonah Firestone approval]1 =

    "Submitted", "Hold", IF([Jonah Firestone approval]1 =

    "Approved", "Go", IF([Firestone approval]1 =

    "Declined", "No")))

    OR

    =IF([Approval Status]1 =

    "Submitted", "Hold", IF([Approval Statusl]1 =

    "Approved", "Go", IF([Approval Status]1 =

    "Declined", "No")))


    Thanks again!

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ah-ha. I see what you are trying to do now.


    Are all of the approval columns next to each other, or are there other columns in between them?

Sign In or Register to comment.