Formula Question

Hi Everyone! I am trying to create a formula for the following. I have been trying IF, IF/AND, and IF/OR, but I just can't seem to get it right.

I have 5 approval columns in a spreadsheet. Each column has a drop down and the person approving will select approve or reject. I then have a 6th column that will be the final approval column. I am trying to write a formula that will auto update the final approval column based on the inputs in the other 5 columns. If there aren't inputs in all 5 columns, I want the approval column to stay blank or say pending. If all 5 select approve, I want the final column to be approve. The catch is, if one of the columns selects reject, I want that to trigger a reject in the final approval column.

In summary, the formula needs to account for all 5 columns saying approve to mark final approval, any one of the 5 columns saying reject to mark reject in the final column, or stay blank/pending if all 5 columns don't have a response.

Any guidance with the formula would be greatly appreciated! I have been trying to figure it out for a while now and can't quite get it.

Thank you!! 🙂

Find more posts tagged with

Formulas

Accepted answers

All comments

Ricky Woodage

Hi there,

I tested the below and this should do what you need. Replace "Approval range" with the range of five cells and the approval and reject statements with whatever text is used in those cells to say approved or rejected.

IF(COUNTIF("Approval range","Approval Statement")=5,"Approved",IF(CONTAINS("Rejected Statement","Approval range")=True,"Rejected",""))

Hope that helps!

Lzasso

Hi Ricky-

Thanks so much for your response. When you say the range of five cells, how do I input that? The column headers? It's not letting me select the cells into the formula. Apologies, just switching over to Smartsheet from Excel and having trouble getting used to the column differences. I put column titles below as an example.

I.e. Column 1 - Approved by Events Team

Column 2 - Approved by Scheduling Team

Thank you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.