Formula to flag required approvals and if they've been obtained

I'm new to Smartsheet and having some trouble with a formula and helper columns I am creating to display dashboard metrics differently, and to use for conditional formatting needs. The Reviews and Approvals Needed field is a dropdown with 4 approval levels and every project requires different approval combos.

I created the formula below for the Helper-CLT field that tells me if the project was approved or not based on CLT being required and status being a date:

=IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row:[Reviews and Approvals Needed]@row), ISDATE([Conservation Leadership Team (CLT) Review Status]@row)), "Approved", "Pending")

However, I can't figure out the order of a nested IF statement that would actually tell me: If CLT is required and CLT status is a date = "approved"; if CLT is required and CLT status is blank = "pending"; if CLT is not required, returns "N/A".

Is this possible? I keep getting unparseable as the result.

Find more posts tagged with

Accepted answers

Mark.poole

=IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISDATE([Conservation Leadership Team (CLT) Review Status]@row)), "Approved", IF(AND(CONTAINS("CLT", [Reviews and Approvals Needed]@row), ISBLANK([Conservation Leadership Team (CLT) Review Status]@row)), "Pending", "N/A"))

Paul Newcome

My initial suggestion does leave some room open for issues, but assuming there will never be dates in the date fields that aren't "tagged", you could use something along the lines of:

=IF(COUNTM([reviews and Approvals Needed]@row) = COUNTIFS([Fist Date Column Name]@row:[Last Date Column Name]@row, ISDATE(@cell)), "Yes", "No")

The idea here is that we count how many approvals are needed and compare that to how many dates we have in the date columns. If those two numbers match, we are good to go.

All comments

Mark.poole

Mark.poole

@Lauren Lederle When you are using @row you do not need to hyphen. As the range is literally the column in the same row your looking at. You only need to column:column if you are searching the entire column for some information

Lauren Lederle

Thank you Mark, that worked like a charm and I appreciate the tip!

Paul Newcome

You can also simplify this a little further like so:

=IF(HAS([Reviews And Approvals Needed]@row, "CLT"), IF([Conservation Leadership Team (CLT) Review Status]@row <> "", "Approved", "Pending"), "N/A")

When you find yourself repeating the same part in multiple AND statements, it can usually be simplified by using the first IF to specify the repeated portion of the AND and then a second IF to parse out the different pieces.

=IF(this is true for all instances, IF(this other thing is true, "true", "not true"), "first thing isn't true")

Mark.poole

@Paul Newcome I would of recommended the same thing. How ever I did not just want to assume dates are going to be the only value in

Conservation Leadership Team (CLT) Review Status

Paul Newcome

@Mark.poole You could have done the same with the IF statement and just used the ISDATE function for the date itself. The main point of my post was to give another option when you have the same thing listed in multiple AND functions. The below will still remove the need for the AND functions and check for dates specifically. You will see it is the same overall structure.

=IF(HAS([Reviews And Approvals Needed]@row, "CLT"), IF(ISDATE([Conservation Leadership Team (CLT) Review Status]@row), "Approved", "Pending"), "N/A")

It still follows the same format for cutting out the ANDs.

=IF(this is true for all instances, IF(this other thing is true, "true", "not true"), "first thing isn't true")

Lauren Lederle

Thank you both, I've learned so much from this thread and your comments on other threads as I was troubleshooting my issues this week.

Mark.poole

@Paul Newcome I get what your saying now.

Paul Newcome

@Lauren Lederle Happy to help. 👍️

@Mark.poole Awesome! It doesn't make a huge difference here because it is only two AND statements, but in instances where you have it across 5 or 6, it really cuts down on the typing.

Lauren Lederle

@Paul Newcome and @Mark.poole, I have a follow up question if you are able to assist… Is there a formula that can create a yes/no symbol to indicate if all required approvals for a particular project have been obtained? In the screenshot below, I'm showing how I want the "All Approvals Received?" field to populate based on the "Reviews and Approvals Needed" tags and the status of approvals for each project. But I just cant figure out the formula needed. Thanks.

Screenshot 2025-05-20 142205.png

Paul Newcome

My initial suggestion does leave some room open for issues, but assuming there will never be dates in the date fields that aren't "tagged", you could use something along the lines of:

=IF(COUNTM([reviews and Approvals Needed]@row) = COUNTIFS([Fist Date Column Name]@row:[Last Date Column Name]@row, ISDATE(@cell)), "Yes", "No")

The idea here is that we count how many approvals are needed and compare that to how many dates we have in the date columns. If those two numbers match, we are good to go.

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.