Formula for Status signs based on multiple columns

Shirley Pruitt

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.

Etienne Mermillod

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 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".

Shirley Pruitt

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".

Shirley Pruitt

@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!

Shirley Pruitt

@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 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.

Shirley Pruitt

@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 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?

Shirley Pruitt

@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 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?

Sue Emery

I see there wasn't any follow-through.

I am having the same question, but I only need 2 columns to work with for approval.

I need to happen is if either decline then need to record in 3rd column "no"

If one approves and other declines then need to record in 3rd column "no"

If both approve then need to record in 3rd column "yes"

And yes, to your question are there other columns in between them?

Paul Newcome

@Sue Emery If you are only working with 2 approval columns, then you should be able to use something along the lines of...

=IF(AND([1st Approval Column]@row = "Approved", [2nd Approval Column]@row = "Approved"), "yes", "no")

Sue Emery

I've used this formula and it will get me "no" in the 3rd column if one says "no" and other says "yes".

=IF(CONTAINS("No", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "No", IF(CONTAINS("Yes", [Meg Hummel approval]@row:[Thomas Payne approval]@row), "Yes"))

What I'm having a problem with is if one says "Yes" then the Approval Status column (3rd column) says "Yes" and notifies that chemical is approved, before the 2nd person can approve it. I need the Approval Status column (3rd column) to not change to "Yes" until both approve, or both their approval columns are "Yes".

Paul Newcome

@Sue Emery Right. So try the formula I posted above except update your column names like so...

=IF(AND([Meg Hummel approval]@row = "yes", [Thomas Payne approval]@row = "yes"), "yes", "no")

The way this reads is that if BOTH are "yes" then it will output "yes". All other combinations including blanks, no's, or any other data will output a "no" until both are "yes".

Sue Emery

Worked Great!! Thanks!