Nested if/then/and/or???

Hello out there, attaching a screenshot for reference. I'm trying to get the far right column ("Completely Processed") to check under a series of different circumstances:

  1. 'Check Cut to Vendor' box MUST be checked for all cases
  2. If none of the 'outside $$' boxes are checked and 'Check Cut to Vendor' is cut, then the 'Completely Processed' cell gets checked
  3. This is what has been done so far (and works for step 2): =IF(AND([Check Cut to Vendor]@row = 1, [1 is Outside $$]@row = 0, [2 is outside $$]@row = 0, [3 is Outside $$]@row = 0), 1)
  4. This is where I'm stuck: If any of the 3 'X is Outside $$' boxes are checked, then the corresponding 'Funding Code X Reimbursement Received' cell must have a date put into it before the 'Completely Processed' box gets checked. If more than 1 code is outside funding, then all appropriate cells need to be dated.

任何帮助公司ld be greatly appreciated, thanks!

Gary


CheckAndDateBoxes.png


Answers

  • Danielle Arteaga
    Danielle Arteaga ✭✭✭✭✭✭
    edited 05/21/20

    Are you looking to automate entry of a date into the Funding Codencell, or will that be manually entered?

  • Hi Danielle, the only thing to be automated is the "Completely Processed" cell, all others are manually entered (or left blank as appropriate). So there are a few different points where the "C-P" cell could be checked, depending on the number of outside reimbursements required (0-3). Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Try an @cell reference in your COUNTIFS.<\/p>

=IF(COUNTIFS([Column Name]:[Column Name], @cell = <\/strong>[Column Name]@row)> 1, 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":111819,"type":"question","name":"Hello, i need some help how do i connect the %complete column to the status column","excerpt":"for example if the status says in progress i want the %complete column to automatically say 50% can anyone help me?","snippet":"for example if the status says in progress i want the %complete column to automatically say 50% can anyone help me?","categoryID":322,"dateInserted":"2023-10-18T12:57:18+00:00","dateUpdated":"2023-10-18T13:14:36+00:00","dateLastComment":"2023-10-18T15:01:33+00:00","insertUserID":167940,"insertUser":{"userID":167940,"name":"Younes Amine","url":"https:\/\/community.smartsheet.com\/profile\/Younes%20Amine","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-18T14:41:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":167940,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-18T17:34:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":12,"countViews":57,"score":null,"hot":3395282331,"url":"https:\/\/community.smartsheet.com\/discussion\/111819\/hello-i-need-some-help-how-do-i-connect-the-complete-column-to-the-status-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111819\/hello-i-need-some-help-how-do-i-connect-the-complete-column-to-the-status-column","format":"Rich","tagIDs":[254,319,334,472],"lastPost":{"discussionID":111819,"commentID":400577,"name":"Re: Hello, i need some help how do i connect the %complete column to the status column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400577#Comment_400577","dateInserted":"2023-10-18T15:01:33+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-10-18T17:34:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-18T14:44:40+00:00","dateAnswered":"2023-10-18T14:33:30+00:00","acceptedAnswers":[{"commentID":400564,"body":"

It looks like you are using dependencies. In that case you will not be able to use a formula in any column being used by the dependencies.<\/p>


<\/p>

You will need to flip your logic so that the Status us generated by the % Complete, and the % Complete will have to be the manual entry.<\/p>


<\/p>

We could also use a couple of Change Cell automations instead of a formula in the Status column to allow for manual selection of \"Waiting\" as needed.<\/p>


<\/p>

The automation would be set up to run whenever the % Complete changes to any value. You would use a condition of the % Complete being zero to output Not Started, condition of one to output Complete, and condition of greater than zero and less than one to output In Progress.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"},{"tagID":319,"urlcode":"functionality","name":"functionality"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":111820,"type":"question","name":"Look for matching fields in multiple Smartsheet's and return an adjacent field.","excerpt":"I have an ID number that can be located in one of multiple Smartsheet's. I would like to look for and match this ID number and return a field from the same row from whichever Smartsheet (SS)matches the ID number. I have multiple formulas that allow me to find the matching ID# from only one SS: 1) =VLOOKUP([AIE-ID#]@row,…","snippet":"I have an ID number that can be located in one of multiple Smartsheet's. I would like to look for and match this ID number and return a field from the same row from whichever…","categoryID":322,"dateInserted":"2023-10-18T12:58:10+00:00","dateUpdated":null,"dateLastComment":"2023-10-18T14:44:56+00:00","insertUserID":146030,"insertUser":{"userID":146030,"name":"CNC","url":"https:\/\/community.smartsheet.com\/profile\/CNC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-18T16:46:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":146030,"lastUser":{"userID":146030,"name":"CNC","url":"https:\/\/community.smartsheet.com\/profile\/CNC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-18T16:46:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":32,"score":null,"hot":3395276586,"url":"https:\/\/community.smartsheet.com\/discussion\/111820\/look-for-matching-fields-in-multiple-smartsheets-and-return-an-adjacent-field","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111820\/look-for-matching-fields-in-multiple-smartsheets-and-return-an-adjacent-field","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":111820,"commentID":400571,"name":"Re: Look for matching fields in multiple Smartsheet's and return an adjacent field.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/400571#Comment_400571","dateInserted":"2023-10-18T14:44:56+00:00","insertUserID":146030,"insertUser":{"userID":146030,"name":"CNC","url":"https:\/\/community.smartsheet.com\/profile\/CNC","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-18T16:46:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png","urlSrcSet":{"10":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=10\/https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png","300":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=300\/https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png","800":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=800\/https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png","1200":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1200\/https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png","1600":"https:\/\/us.v-cdn.net\/cdn-cgi\/image\/fit=scale-down,width=1600\/https:\/\/us.v-cdn.net\/6031209\/uploads\/G8QG6SFN5V8U\/ieevalss-png.png"},"alt":"ieevalSS.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-10-18T14:45:28+00:00","dateAnswered":"2023-10-18T14:44:56+00:00","acceptedAnswers":[{"commentID":400571,"body":"

@Nick Korna<\/a> - This is great! The 3rd IFERROR was missing. With that, the formula works great. THANK YOU SOOOO MUCHHHH!!<\/p>

WORKED: <\/p>

=IFERROR(INDEX({No Action[s] Range 3}, MATCH([AIE-ID#]@row, {No Action[s] Range 1}, 0)), IFERROR(INDEX({Action[s] Plan Range 2}, MATCH([AIE-ID#]@row, {Action[s] Plan Range 1}, 0)), IFERROR<\/strong>(INDEX({Next FY Consideration Action[s] Range 1}, MATCH([AIE-ID#]@row, {Next FY Consideration Action[s] Range 2}, 0)), \"Not found\")))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions