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

lvolpe
lvolpe ✭
在公式与函数

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

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

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

最佳答案

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

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


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


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

    thinkspi.com

答案

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    如果要填充可用联系人，则每行只能填充一个联系人。目前还不可能在单个cell中填充多个可用的联系人。

    thinkspi.com

  • lvolpe
    lvolpe ✭

    谢谢你,保罗!如果我将联系单元格转换为文本，这样我就可以看到被分配的人的名字，这样可以吗?

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    您可以将其保留为接触类型单元格。它只是不能将它用作诸如自动化或任何由用户筛选的事情的联系人。


    具体如何设置参考表?您是否能够根据需要提供删除、阻止敏感/机密数据和/或用模拟数据替换的屏幕截图?

    thinkspi.com

  • lvolpe
    lvolpe ✭
    编辑07/07/21

    当然可以。见下文。理想情况下，标题为CPL、Circuit和Region的列将自动填充与主表中选择的县相对应的值。

    image.png


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

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


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


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

    thinkspi.com

  • lvolpe
    lvolpe ✭

    三声欢呼——效果很好。谢谢你！

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    很乐意帮忙。️

    thinkspi.com

  • 梅勒妮Proebstel
    梅勒妮Proebstel ✭

    @Paul新来的你能在这方面帮助我吗?我有一个多选择下拉7个选项。当选择这两个选项时，我试图创建一个IF语句，将单元格值填充为12.5。

    我在公式上不走运。

    image.png


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

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

@keesuri25<\/a> <\/p>

Your formula is working for me:<\/p>

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

What type of column is YTD on your sheet? It needs to be a Date type column.<\/p>"},{"commentID":346398,"body":"

It looks like you have a minor syntax issue. Whenever you reference a column name that has a space, number, and\/or special character in it, you have to wrap the column name in square brackets whereas you wrapped the entire reference in square brackets. Try this:<\/p>

=COUNTIFS([Engagement Phase]:[Engagement Phase], \"COMPLETED\", [PoC End date]<\/strong>:[<\/strong>PoC End date], YEAR(@cell) = 2022)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-07 00:22:30","updateUser":{"userID":153291,"name":"keesuri25","url":"https:\/\/community.smartsheet.com\/profile\/keesuri25","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T00:19:47+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":96311,"type":"question","name":"INDEX \/ MATCH Formula Question","excerpt":"Hello, I am missing something with executing this formula. This is my first time trying to apply to my sheet. I can only successfully return a #Unparseable error. 😥 Would appreciate any guidance you can provide. Formula: =INDEX({CI Weight}), MATCH([Task Name]@row, {Project Name}, 0) Goal: Pull in ranking (CI Weight) from…","categoryID":322,"dateInserted":"2022-10-06T17:01:23+00:00","dateUpdated":null,"dateLastComment":"2022-10-06T23:25:11+00:00","insertUserID":134324,"insertUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":134324,"lastUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":53,"score":null,"hot":3330178594,"url":"https:\/\/community.smartsheet.com\/discussion\/96311\/index-match-formula-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96311\/index-match-formula-question","format":"Rich","lastPost":{"discussionID":96311,"commentID":346402,"name":"Re: INDEX \/ MATCH Formula Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346402#Comment_346402","dateInserted":"2022-10-06T23:25:11+00:00","insertUserID":134324,"insertUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+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\/LKAW8HFTGVXC\/index-match-question.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"INDEX MATCH question.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-06T20:52:07+00:00","dateAnswered":"2022-10-06T20:47:41+00:00","acceptedAnswers":[{"commentID":346374,"body":"

Remove the ) after CI WEIGHT<\/p>"},{"commentID":346375,"body":"

My hero!! Muchas gracias, @Michael Culley<\/a> ! 🤩<\/span>🤩<\/span><\/p>"},{"commentID":346400,"body":"

If you want it to only apply to parent rows then it would look more like this:<\/p>

=IF(COUNT(CHILDREN([Task Name]@row)) <> 0, <\/strong>INDEX({CI Weight}, MATCH([Task Name]@row, {Project Name}, 0)))<\/strong><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-06 20:52:07","updateUser":{"userID":134324,"name":"Marcela Hernandez","url":"https:\/\/community.smartsheet.com\/profile\/Marcela%20Hernandez","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-07T03:32:32+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":96312,"type":"question","name":"Can I use the Countif function to count the amount of times one column is greater than another?","excerpt":"For example, in my Smartsheet I have two columns that I would like to compare data. \"Sprinkler Head Count (Estimated)\" versus \"Sprinkler Head Count (Actual).\" I would like to count how many times in my sheet where the Actual head count is greater than the Estimated head count. Any ideas how we can achieve this? Thanks in…","categoryID":322,"dateInserted":"2022-10-06T17:10:54+00:00","dateUpdated":null,"dateLastComment":"2022-10-06T17:16:52+00:00","insertUserID":151299,"insertUser":{"userID":151299,"name":"lcain","url":"https:\/\/community.smartsheet.com\/profile\/lcain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-06T20:25:53+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\/AItbvmniEdReOVTwaHHrhgrOiwux3krYT43KIqrvf5FW=s96-c","dateLastActive":"2022-10-07T00:06:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3330154666,"url":"https:\/\/community.smartsheet.com\/discussion\/96312\/can-i-use-the-countif-function-to-count-the-amount-of-times-one-column-is-greater-than-another","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96312\/can-i-use-the-countif-function-to-count-the-amount-of-times-one-column-is-greater-than-another","format":"Rich","lastPost":{"discussionID":96312,"commentID":346312,"name":"Re: Can I use the Countif function to count the amount of times one column is greater than another?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/346312#Comment_346312","dateInserted":"2022-10-06T17:16:52+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\/AItbvmniEdReOVTwaHHrhgrOiwux3krYT43KIqrvf5FW=s96-c","dateLastActive":"2022-10-07T00:06:50+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\/XULU5EB0Y7VE\/estimate-v-actual.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Estimate v Actual.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-06T17:15:39+00:00","dateAnswered":"2022-10-06T17:14:49+00:00","acceptedAnswers":[{"commentID":346310,"body":"

create a checkbox column with this formula: <\/p>

=if(Sprinkler Head Count (Actual) >Sprinkler Head Count (Estimated),1,0)<\/p>

Then you can do a countif statement counting the checkboxes: =countif([checkbox column]:[checkbox column],1)<\/p>"},{"commentID":346312,"body":"

@lcain<\/a> <\/p>

I'm not sure if it can be done with a single formula. Here's what I would do.<\/p>

Create a helper column that's a checkbox column called something like \"Sprinkler (helper)\". Write this formula into it:<\/p>

=IF([Sprinkler Head Count (Actual)]@row>[Sprinkler Head Count (Estimated)]@row, 1)<\/p>

Then hide that column since you don't need to look at it on your sheet. Then in the column you want the count use this formula:<\/p>

=COUNTIF([Sprinkler (helper)]:[Sprinkler (helper)], 1)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-06 17:15:39","updateUser":{"userID":151299,"name":"lcain","url":"https:\/\/community.smartsheet.com\/profile\/lcain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-06T20:25:53+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":[]}">