使用内容
类别列表
I am looking to create a formula that uses the IMPACTED REGION and IMPACTED FUCTIONAL AREAS to result in a Name to be populated in the IMPACTED & RESPONSIBLE RISK OWNER column. the 1st 2 columns (REGION + AREAS) are multiple selectable entries. <\/p>
Example:<\/p>
Regions column: US LATAM EMEA APAJ <\/p>
Areas column: HR - Marketing - Operations - etc...<\/p>
Owners column: Name 1, 2, 3 etc <\/p>
example:<\/p>
Region = US<\/p>
Area = HR & Operations<\/p>
Name = Rick (desired name to populate the Owner cell) etc.. <\/p>
(if the IMPACT Region listed US and IMPACT Area listed either HR or Operations, then Owner Name 'Rick' would populate the Owner cell) <\/p>
What is the proper formula string to create an IF AND OR selection to then select a Name for the Owner? <\/p>
*I have run into issues where only the top name in the menu only gets selected (ie. if Rick were 1st on top with Jane and Joe following as 2nd and 3rd, those names don't get picked up in a formula string).<\/p>
thanks for your assistance!<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/SDE7GRUL8I3L\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"},{"commentID":326892,"body":"
Since you're using multi-select cells, using the HAS function is key. HAS will treat each standalone value inside a multi-select cell as a distinct value. For example, if I wrote this IF on Row 1 of your screenshot, it would equal \"Failure\":<\/p>
=IF([::IMPACTED REGIONS(S)]@row = \"US\", \"Success\", \"Failure\")<\/p>
But if I use HAS for my logical statement, I would get \"Success\":<\/p>
=IF(HAS([::IMPACTED REGIONS(S)]@row, \"US\")<\/strong>, \"Success\", \"Failure\")<\/p> Using this, let's get your IF\/AND\/OR\/HAS syntax right:<\/p> =IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, \"US\"), HAS([::IMPACTED REGION(S)]@row, \"CAD\")), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, \"Insurance\")), \"NoName\", \"//www.santa-greenland.com/community/categories/using-smartsheet/\")<\/p> Just to help you see exactly how the pieces come together, we're placing an OR inside the AND, along with the other criteria. The yellow is the AND statement, with the yellow comma and yellow parentheses at the end showing you the parts of the AND. The blue is the embedded OR, the green is the rest of the criteria for the AND, though this could be another OR as well. Taken together, the entire AND and its parts make up the logical statement portion of the IF:<\/p> The result of this formula for Row 2 would be \"NoName\".<\/p> Here is the above in use with two OR statements embedded in the AND, from my test sheet. The data matches one of the two options in each OR statement. <\/p> Change one of the multi-select cells to remove all of the criteria that would match, then I get \"Fail\":<\/p> Just go up to the File Menu and choose Save As Template.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-09 17:40:10","updateUser":{"userID":154740,"name":"Priya_157","url":"https:\/\/community.smartsheet.com\/profile\/Priya_157","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-09T18:00:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":97555,"type":"question","name":"how do I put created date column values from Excel into Smartsheet column type created date column?","excerpt":"I have imported an excel sheet into Smartsheet. After importing the data, Smartsheet set the column type for that created date column to be 'Date' But when I changed column type to created date then all values in the column vanished and then today's date and default set time e.g 11\/09\/22 8:16 AM appeared for all values in…","categoryID":321,"dateInserted":"2022-11-09T17:11:54+00:00","dateUpdated":null,"dateLastComment":"2022-11-09T17:44:16+00:00","insertUserID":154763,"insertUser":{"userID":154763,"name":"Saad","url":"https:\/\/community.smartsheet.com\/profile\/Saad","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-09T20:57:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":145151,"lastUser":{"userID":145151,"name":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu2swDSBf8UVqLz0TNb7O4SvBO4L_SdaeBQp4xqD=s96-c","dateLastActive":"2022-11-09T18:51:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":15,"score":null,"hot":3336030370,"url":"https:\/\/community.smartsheet.com\/discussion\/97555\/how-do-i-put-created-date-column-values-from-excel-into-smartsheet-column-type-created-date-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97555\/how-do-i-put-created-date-column-values-from-excel-into-smartsheet-column-type-created-date-column","format":"Rich","lastPost":{"discussionID":97555,"commentID":350568,"name":"Re: how do I put created date column values from Excel into Smartsheet column type created date column?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/350568#Comment_350568","dateInserted":"2022-11-09T17:44:16+00:00","insertUserID":145151,"insertUser":{"userID":145151,"name":"Mike TV","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20TV","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu2swDSBf8UVqLz0TNb7O4SvBO4L_SdaeBQp4xqD=s96-c","dateLastActive":"2022-11-09T18:51:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-09T18:30:23+00:00","dateAnswered":"2022-11-09T17:44:16+00:00","acceptedAnswers":[{"commentID":350568,"body":" @Saad<\/a> <\/p> No. You'll have to use a column called Created Date and just have it set to a date type. Then you'll need to create an automation for when a new row is added to add a date to your Created Date column.<\/p> I agree that this isn't the best way to go about this so I'd suggest submitting an Enhancement Request to SmartSheet to see if they can add a feature to the Import Excel File sheet creation that would take any column in the Excel file called Created Date and feed those dates into the system generated Created Date column type. While you're at it, suggest that they allow the Created Date column to have it's date formatting changed because currently they do not allow that.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-09 18:30:23","updateUser":{"userID":154763,"name":"Saad","url":"https:\/\/community.smartsheet.com\/profile\/Saad","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-09T20:57:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":97546,"type":"question","name":"Multiple contacts and auto notification","excerpt":"If I set up my Assigned To column as multiple contacts, does the auto notification still work?","categoryID":321,"dateInserted":"2022-11-09T15:06:59+00:00","dateUpdated":null,"dateLastComment":"2022-11-09T17:52:06+00:00","insertUserID":128842,"insertUser":{"userID":128842,"name":"Karen Bruer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Karen%20Bruer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5MPAuTrWZX4!elHDp9qhGX4!HMSKFvshvfI","dateLastActive":"2022-11-09T17:03:59+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-11-10T00:02:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":21,"score":null,"hot":3336024545,"url":"https:\/\/community.smartsheet.com\/discussion\/97546\/multiple-contacts-and-auto-notification","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/97546\/multiple-contacts-and-auto-notification","format":"Rich","lastPost":{"discussionID":97546,"commentID":350572,"name":"Re: Multiple contacts and auto notification","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/350572#Comment_350572","dateInserted":"2022-11-09T17:52:06+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-11-10T00:02:05+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-11-09T18:15:21+00:00","dateAnswered":"2022-11-09T15:56:13+00:00","acceptedAnswers":[{"commentID":350530,"body":" Hi @Karen Bruer<\/a> <\/p> I hope you're well and safe!<\/p> Yes, it does! <\/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>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-11-09 18:15:21","updateUser":{"userID":91566,"name":"Genevieve P.","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/T4YLN045Q7TF\/n81H11L2YPG9T.jpg","dateLastActive":"2022-11-09T21:38:31+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}}},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","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":[]}],"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}"><\/img><\/a>\n <\/div>\n<\/div>\n<\/img><\/a>\n <\/div>\n<\/div>\n受欢迎的标签
