Multiple conditions withing an IF formula

I am extremely new to smartsheet and I'm having problems understanding the formatting for multiple conditions within an IF(AND()) formula.

I want to have a status show complete only when multiple columns are filled in.

So far I have the date =IF([Date Complete]@row = TODAY(0), "Complete")

The issues comes when I have to add text as well as approved conditions.

The columns are as follows that I need filled first to reach my end goal

Trainee Initials - text

Trainer initials - Text

Trainer Approval - Dropdown (approved)

Training Department Approval - Dropdown (approved)

The dropdown I believe would be

=IF(AND([Date Complete]@row = TODAY(0), [Trainer Approval@row] = "Approved", [Training Department Approval@row] = "Approved", "Complete"). I'm just not sure how to include initials or even if what I have is correct.

It's going to be converted into a column formula and the approvals are automated. Once everything for that task is complete, the status would change and the whole role becomes green.

Any help is greatly appreciated. Thank you!

Find more posts tagged with

Accepted answers

kelly906

Hello! Understood your criteria to be those listed below in order to consider a row "complete".

Must be todays date
Both Trainee and Trainer must both have entered anything into the initials fields (not null)
Both Trainer and Training Dept must have specified 'approved'

I used a checkbox field to indicate "complete".

=IF(AND([Completed Date]@row = TODAY(), [Trainee Initials]@row <> "", [Trainer Initials]@row <> "", [Trainer Approval]@row = "Approved", [Training Department Approval]@row = "Approved"), 1, 0)

-----------------------------------------------------------------------------

If I misunderstood and you want the "completed" date to be the date the "completed" box was checked, you would just need to remove the date component, then create a "record a date" automation to drop today's date with the checkbox turns to 'checked':

=IF(AND([Trainee Initials]@row <> "", [Trainer Initials]@row <> "", [Trainer Approval]@row = "Approved", [Training Department Approval]@row = "Approved"), 1, 0)

Hope this works and is what you're looking for!

All comments

kelly906

Hello! Understood your criteria to be those listed below in order to consider a row "complete".

Must be todays date
Both Trainee and Trainer must both have entered anything into the initials fields (not null)
Both Trainer and Training Dept must have specified 'approved'

I used a checkbox field to indicate "complete".

=IF(AND([Completed Date]@row = TODAY(), [Trainee Initials]@row <> "", [Trainer Initials]@row <> "", [Trainer Approval]@row = "Approved", [Training Department Approval]@row = "Approved"), 1, 0)

-----------------------------------------------------------------------------

=IF(AND([Trainee Initials]@row <> "", [Trainer Initials]@row <> "", [Trainer Approval]@row = "Approved", [Training Department Approval]@row = "Approved"), 1, 0)

Hope this works and is what you're looking for!

aNewTPManager

@kelly906 Thank you for the response. The completed was actually a drop down of either completed or not but I was able to get that taken care of. Thank you

aNewTPManager

@kelly906 If I could ask one more question.

If i wanted to check that if all the children "trainee initials" are filled, to pull that same text into the parent. Would that be possible?

I found that =JOIN((DESCENDANTS())) works but it does it for any child that filled. I would like it for ,once all descendants are filled in, to populate. I feel that there should be an AND function but I can't figure out the syntax

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.