Options
aNewTPManager ✭✭✭
02/28/23 in Formulas and Functions
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!
0 · Share on FacebookShare on Twitter
kelly906 ✭✭✭
03/01/23 Answer ✓
Options
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!
1 · Share on FacebookShare on Twitter
Answers
kelly906 ✭✭✭
03/01/23 Answer ✓
Options
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!
1 · Share on FacebookShare on Twitter
aNewTPManager ✭✭✭
03/01/23
Options
@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
0 · Share on FacebookShare on Twitter
aNewTPManager ✭✭✭
03/01/23
Options
@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
0 · Share on FacebookShare on Twitter
Help Article Resources
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("searchbox"+index); Coveo.initSearchbox( searchBoxRoot, "https://search.smartsheet.com/community/" ); }); $('#titleBar .CoveoSearchbox').hide(); $('.coveoSearchButton').on("click",function(){ $('#titleBar .CoveoSearchbox').toggle(); }); // ====== Front Page Search Box ====== $('#Form_search').remove(); var boxes = ["#search-0", "#search-1", "#search-2", "#search-3", "#search-4", "#search-5"]; console.log("PT: Before search box replacement"); $(boxes).each(function(i, val) { // ====== Front Page Search Box ====== $(val).each(function( index ) { console.log(this); $('#searchbox' + i).remove(); $(this).find('form').remove(); $(this).addClass('fpsearchbox'); $(this).attr('id', 'fpsearchbox' + index); $(this).append('
'); $(this).css('min-height', '20px'); const searchBoxRoot = document.getElementById("fpsearchbox"+index); var action = "https://search.smartsheet.com/community/"; if ( $('body').hasClass('Categories')) { var category=$('h1.heading-1').text(); if (category === "Smartsheet Product Feedback & Ideas") { category = encodeURIComponent(category); Coveo.$(searchBoxRoot).on('buildingQuery', function(e, args) { args.queryBuilder.advancedExpression.addFieldExpression('@communitycategory', '==', [category]); }); action = action + '#&f:@communitycategory=['+category+']'; } } Coveo.initSearchbox( searchBoxRoot, action ); }); }); });
Categories
- All Categories
- 14 Welcome to the Community
- 59.4K Get Help
- 90 Global Discussions
- 94 Industry Talk
- 400 Announcements
- 12 Community Corner Newsletter
- 71 Brandfolder
- 121 Just for fun
- 46 Community Job Board
- 22 Member Spotlight
- 1 SmartStories
- 251 Events
- 13 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
