使用内容
类别列表
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>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/QO5O3UX0YVL4\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nThe 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>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/641Q5QKMZ2ZR\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nChange one of the multi-select cells to remove all of the criteria that would match, then I get \"Fail\":<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/VO8FGETBG6W0\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
<\/p>"},{"commentID":327103,"body":"
Thanks Jeff and Andrea, yes i was thinking along those lines as well, some column names are long and the menu options are as well, will be working on reducing those to see if i can make it fit. I may also look to offload the match outputs to another column and x-ref them but it may be a wash in terms of char count. thanks again !<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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":92828,"type":"question","name":"Forms: How to print\/generate a PDF with field label and answers","excerpt":"I am creating a survey form, and the questions are quite long. There is a character limit for the column\/field name, so I just use numbers (e.g. \"question 1\", \"question 2\") in the column\/field name and put the actual questions in the field label. That seems to work for the form. However, I want to be able to print\/generate…","categoryID":321,"dateInserted":"2022-06-30T17:45:09+00:00","dateUpdated":null,"dateLastComment":"2022-11-09T22:16:38+00:00","insertUserID":149657,"insertUser":{"userID":149657,"name":"HT123456","url":"https:\/\/community.smartsheet.com\/profile\/HT123456","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!-7NcdxLeJvw!xZHeMZrHM3w!rmHwQBhsQfX","dateLastActive":"2022-06-30T17:45:12+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":154437,"lastUser":{"userID":154437,"name":"Sherri Sherri","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sherri%20Sherri","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-09T22:28:04+00:00","banned":0,"punished":0,"private":true,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":56,"score":1,"hot":3324644807,"url":"https:\/\/community.smartsheet.com\/discussion\/92828\/forms-how-to-print-generate-a-pdf-with-field-label-and-answers","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/92828\/forms-how-to-print-generate-a-pdf-with-field-label-and-answers","format":"Rich","tagIDs":[204,250,445],"lastPost":{"discussionID":92828,"commentID":350623,"name":"Re: Forms: How to print\/generate a PDF with field label and answers","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/350623#Comment_350623","dateInserted":"2022-11-09T22:16:38+00:00","insertUserID":154437,"insertUser":{"userID":154437,"name":"Sherri Sherri","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Sherri%20Sherri","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-09T22:28:04+00:00","banned":0,"punished":0,"private":true,"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":2,"attributes":{"question":{"status":"answered","dateAccepted":null,"dateAnswered":"2022-07-01T23:36:59+00:00","acceptedAnswers":[]}},"status":{"statusID":2,"name":"Answered","state":"open","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-07-01 23:36:59","updateUser":{"userID":145683,"name":"JCluff","url":"https:\/\/community.smartsheet.com\/profile\/JCluff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-11-07T20:26:47+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":1}],"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":250,"urlcode":"Printing","name":"Printing"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"}]}],"title":"Recent Discussions","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
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":"
I hope you're well and safe!<\/p>
Be safe, and have a fantastic week!<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>
