需要多选下拉列公式，以在另一列中产生多个值

lvolpe
lvolpe ✭
在公式与函数

我一直在尝试使用几个大小公式(VLOOKUP、INDEX、MATCH和两者之间的每个组合)，根据组织所服务的县将组织分配给一个或多个联系人。例如，我有一个多选择下拉列，其中可以选择一个或多个县，我希望分配的列自动填充监督这些县的个人。

我在参考表中创建了一个单独的表，希望这有助于解决问题;VLOOKUP块工作的前半部分，但我似乎不能得到名称填充分配的列。任何建议都非常感谢!

谢谢你帮我解决这个问题!

· 在Facebook上分享 在推特上分享

最佳答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    好的。要拉拢多人，你需要的是……


    =JOIN(DISTINCT(COLLECT({参考表CPL列}，{参考表县级列}，HAS([源表县级列]@row， @cell)))，CHAR (10)）


    CHAR(10)是换行符。您可以将该部分更改为您想要的分隔符。如果坚持使用CHAR(10)，请确保启用文本换行。

    thinkspi.com

    · 在Facebook上分享 在推特上分享

答案

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

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

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>"}]}},"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-17T14:07:49+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"}]},{"discussionID":96644,"type":"question","name":"Today Forumula Question","excerpt":"So I'm still learning formula's. I'm trying to create a formula in my sheet summary. I'm looking for a count of end dates thats within the next 4 days. from today. Here is my formula: =COUNTIF([End Date]:[End Date], >TODAY(4))","categoryID":322,"dateInserted":"2022-10-16T21:31:24+00:00","dateUpdated":null,"dateLastComment":"2022-10-17T15:57:19+00:00","insertUserID":127268,"insertUser":{"userID":127268,"name":"Reggie Andaya","url":"https:\/\/community.smartsheet.com\/profile\/Reggie%20Andaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-17T17:52:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-17T17:31:44+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":27,"score":null,"hot":3331979923,"url":"https:\/\/community.smartsheet.com\/discussion\/96644\/today-forumula-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96644\/today-forumula-question","format":"Rich","lastPost":{"discussionID":96644,"commentID":347472,"name":"Re: Today Forumula Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347472#Comment_347472","dateInserted":"2022-10-17T15:57:19+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-17T17:31:44+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"}},"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,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-17T17:14:31+00:00","dateAnswered":"2022-10-17T15:57:19+00:00","acceptedAnswers":[{"commentID":347472,"body":"

No problem, glad you got it working.<\/p>

Would you mind marking the reply as the \"Answer\"? It will help people who might be searching for this solution find it faster.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-17 17:14:31","updateUser":{"userID":127268,"name":"Reggie Andaya","url":"https:\/\/community.smartsheet.com\/profile\/Reggie%20Andaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-17T17:52:48+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":96636,"type":"question","name":"Health status in a project plan","excerpt":"Hi, I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green, Grey based on Status and Due Date. If the status is complete, grey. If the status is not complete and the due date is more than 5 working dates days in the past , then yellow. If the status is not complete and the due date is…","categoryID":322,"dateInserted":"2022-10-16T11:19:41+00:00","dateUpdated":"2022-10-16T14:48:00+00:00","dateLastComment":"2022-10-17T16:27:45+00:00","insertUserID":148096,"insertUser":{"userID":148096,"name":"Yaya","url":"https:\/\/community.smartsheet.com\/profile\/Yaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-17T16:41:41+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":148096,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-17T17:31:44+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":77,"score":null,"hot":3331948046,"url":"https:\/\/community.smartsheet.com\/discussion\/96636\/health-status-in-a-project-plan","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96636\/health-status-in-a-project-plan","format":"Rich","lastPost":{"discussionID":96636,"commentID":347483,"name":"Re: Health status in a project plan","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347483#Comment_347483","dateInserted":"2022-10-17T16:27:45+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-17T17:31:44+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"}},"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,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-17T14:16:02+00:00","dateAnswered":"2022-10-17T12:40:00+00:00","acceptedAnswers":[{"commentID":347396,"body":"

Sorry, I misspelled Gray and then had a parentheses in the wrong spot. This should do it.<\/p>

=IF(Status@row = \"Complete\", \"Gray\", IF(OR(Status@row = \"Blocked\", Status@row = \"On Hold\"), \"Red\", IF([Due Date]@row <= TODAY(-10), \"Red\", IF([Due Date]@row <= TODAY(-5), \"Yellow\", \"Green\"))))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-17 14:16:01","updateUser":{"userID":148096,"name":"Yaya","url":"https:\/\/community.smartsheet.com\/profile\/Yaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-17T16:41:41+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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">