If, and, or语句

PatriciaR
PatriciaR ✭✭✭✭✭
在公式与函数
image.png

嗨

我是Smartsheets的新手，很难根据财务、EHS和Tech Qual的状态来更新我的总体状态

如果所有三个都是批准或N/A，那么总体状态=批准

如果至少有一个是In Progress或空白，那么总体状态= In Progress

如果至少有一个是未批准的，那么总体状态=未批准


谢谢你的帮助

· 在Facebook上分享 在Twitter上分享

答案

  • Andrew1983
    Andrew1983 ✭

    “整体地位”公式

    =IF(CONTAINS("未批准"，[财务状况]@行:[技术合格状况]@行)，"未批准"，IF(CONTAINS("进行中"，[财务状况]@行:[技术合格状况]@行)，"进行中"，"已批准"))

    · 在Facebook上分享 在Twitter上分享
  • PatriciaR
    PatriciaR ✭✭✭✭✭

    嗨,安德鲁

    哇，你做得太简单了

    最后一条规则…

    我需要所有3个都有一个“批准”或“N/ a”的值在整体状态=“批准”之前

    谢谢你的帮助


    问候

    帕特丽夏

    · 在Facebook上分享 在Twitter上分享

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表引用的公式组合

Smartsheet功能列表

类别

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1,{Job type},\"Contract\"))<\/p>"},{"commentID":347535,"body":"

@Hall<\/a> <\/p>

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, new range, new criteria<\/strong>))<\/p>

=MAX(COLLECT({Jobs Range 2}, {Jobs Range 1}, site1, {Job Type Column}, @cell = \"Contract\"<\/strong>))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-18 07:53:39","updateUser":{"userID":145299,"name":"Hall","url":"https:\/\/community.smartsheet.com\/profile\/Hall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-18T14:26:02+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]},{"discussionID":96680,"type":"question","name":"Adding workdays based on date column AND specific criteria from another column","excerpt":"Hi team - I have a a sheet that has a column for Annual and Semiannual reports. I'd like to create a date in the Next Review Date column based on adding 330 days to the Current Review Date where the Doc Gov Document Revision Schedule entry is Annually (update every 12 months based on anniversary date) and add 150 days to…","categoryID":322,"dateInserted":"2022-10-17T16:47:21+00:00","dateUpdated":"2022-10-17T17:10:31+00:00","dateLastComment":"2022-10-17T20:55:30+00:00","insertUserID":118864,"insertUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-10-18T15:59:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":118864,"lastUserID":132999,"lastUser":{"userID":132999,"name":"Frank S.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/6E46CR0FPMOK\/n4Q8ROUTCGN5X.jpg","dateLastActive":"2022-10-17T22:58:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":42,"score":null,"hot":3332068371,"url":"https:\/\/community.smartsheet.com\/discussion\/96680\/adding-workdays-based-on-date-column-and-specific-criteria-from-another-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96680\/adding-workdays-based-on-date-column-and-specific-criteria-from-another-column","format":"Rich","lastPost":{"discussionID":96680,"commentID":347514,"name":"Re: Adding workdays based on date column AND specific criteria from another column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347514#Comment_347514","dateInserted":"2022-10-17T20:55:30+00:00","insertUserID":132999,"insertUser":{"userID":132999,"name":"Frank S.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Frank%20S.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/6E46CR0FPMOK\/n4Q8ROUTCGN5X.jpg","dateLastActive":"2022-10-17T22:58:17+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/5CBYPQM5IN1C\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-17T20:51:33+00:00","dateAnswered":"2022-10-17T20:24:42+00:00","acceptedAnswers":[{"commentID":347509,"body":"

Hi @jmo<\/a> <\/p>

Here is the formula using the names of the columns you provided. I changed the column names in my testing:<\/p>

=IFERROR(IF(ISBLANK([Doc Gov Document Revision Schedule]@row), \"//www.santa-greenland.com/community/discussion/comment/\", IF([Doc Gov Document Revision Schedule]@row = \"Annually (update every 12 months based on anniversary date)\", (WORKDAY([Current Review Date]@row, 330)), (WORKDAY([Current Review Date]@row, 150)))), \"//www.santa-greenland.com/community/discussion/comment/\")<\/p>

Let me know if that works for the columns you have or if you are all set.<\/p>

Thanks,<\/p>

Frank<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-17 20:51:33","updateUser":{"userID":118864,"name":"jmo","title":"","url":"https:\/\/community.smartsheet.com\/profile\/jmo","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!g0lA5zVbOLw!-wfAd_lcHz4!voKaS5U1cZv","dateLastActive":"2022-10-18T15:59:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}}},"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":[]},{"discussionID":96662,"type":"question","name":"Use Formula to Assign Dropdown Selection in Column","excerpt":"I would like to have the column titled Alert Level return a symbol from the dropdown menu based on the number of selections in the Alert column. I'd like the symbol to be green if there the Alert column for that row is blank. Blue if there is 1 item in the Alert column, Yellow is 2 items and Red if there are 3 or more. Is…","categoryID":322,"dateInserted":"2022-10-17T12:43:43+00:00","dateUpdated":null,"dateLastComment":"2022-10-17T21:24:34+00:00","insertUserID":153573,"insertUser":{"userID":153573,"name":"Gillian","url":"https:\/\/community.smartsheet.com\/profile\/Gillian","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2UtvfrY8Mfo!!nBfUikkOtPJ","dateLastActive":"2022-10-17T22:35:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-18T16:05:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":38,"score":null,"hot":3332057897,"url":"https:\/\/community.smartsheet.com\/discussion\/96662\/use-formula-to-assign-dropdown-selection-in-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96662\/use-formula-to-assign-dropdown-selection-in-column","format":"Rich","tagIDs":[219,254],"lastPost":{"discussionID":96662,"commentID":347520,"name":"Re: Use Formula to Assign Dropdown Selection in Column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347520#Comment_347520","dateInserted":"2022-10-17T21:24:34+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-18T16:05:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/496CBJW297A6\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-17T14:07:46+00:00","dateAnswered":"2022-10-17T12:49:30+00:00","acceptedAnswers":[{"commentID":347398,"body":"

Hi @Gillian<\/a> <\/p>

I hope you're well and safe!<\/p>

Try something like this.<\/p>

=IF(Alerts@row = \"//www.santa-greenland.com/community/discussion/comment/\", \"//www.santa-greenland.com/community/discussion/comment/\", IF(COUNTM(Alerts@row) = 1, \"Blue\", IF(COUNTM(Alerts@row) = 2, \"Yellow\", IF(COUNTM(Alerts@row) = 3, \"Red\"))))<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"},{"commentID":347435,"body":"

@Gillian<\/a> <\/p>

You'd have to add by clicking on the Cog Wheel in the upper right corner.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n

Did that work?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-17 14:07:46","updateUser":{"userID":153573,"name":"Gillian","url":"https:\/\/community.smartsheet.com\/profile\/Gillian","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!2UtvfrY8Mfo!!nBfUikkOtPJ","dateLastActive":"2022-10-17T22:35:00+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">